A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Need formula to extract a numeric value from a free-format text



 
 
Thread Tools Display Modes
  #21  
Old July 18th, 2009, 10:26 AM posted to microsoft.public.excel.misc
Lori
external usenet poster
 
Posts: 673
Default Need formula to extract a numeric value from a free-format tex

After further investigations, i think i've got a simplified version:

=LOOKUP(1E8,--(MID(A4&".",COLUMN(A:IV),7)&" 0/1"))

which returns the last 7 digit integer and errors if none is found.
[&" 0/1" ensures only integers are returned; &"e0" would return decimals too]

"Rick Rothstein" wrote:

I saw that, but was wondering if that 7-digit number could ever be encased
in text like this... abc1234567def... hence my question about a leading
space (assuming the number were not at the beginning of the text) and a
following "dot" (as shown in his example) or possibly a following space to
offset it from other text. I've been playing around with an array-entered**
formula concept like this...

=MID(F5,MIN(IF(ISNUMBER(--MID(SUBSTITUTE(F5," ","x"),
ROW(1:30),7)),ROW(1:30))),7)

**Commit this formula using Ctrl+Shift+Enter, not just Enter by itself

I'm not really concerned about the leading space any more (the SUBSTITUTE
function has taken care of that), but I am interested in what trails the
7-digit number. Of course there are still problems with the above formula;
for example, things constructed like this...17Jul09... and like this...
1.23e45... will register as "7-digit numbers". Unfortunate, the first 7
digits of a number longer than 7 digits will also register as a "hit". I
just figured it would be nice to know if there were *any* formatting type
things to lock onto before attempting to patch the above formula any
further. As a matter-of-fact, if the OP is reading this... I would also like
to know if the text could contain 8-digit or longer numbers, date-looking
text like 17Jul09 and floating point power of ten numbers like 1.23e45?

--
Rick (MVP - Excel)


"JoeU2004" wrote in message
...
"Rick Rothstein" wrote:
I would still be interested in the answer to my questions...


In response to my questions, Eric wrote:

"Unfortunately there's no precise format. I just need to look for a
7-digit
number, which can be anywhere in the string. (I could restrict it to
looking
for the first 7-digit number, since I know that there's never more than
one.)"


----- original message -----

"Rick Rothstein" wrote in message
...
I would still be interested in the answer to my questions...

"Is the number always precede by a space when the number
is interior to the text? What about that "dot" after it... is there
always a dot following it? If the dot might not always be
there, is there always a space after the number when it is
interior to the text?"

--
Rick (MVP - Excel)


"Eric_NY" wrote in message
...
"However, with the morefunc add-in there is an option, I believe it is a
menu
option, to install the add-in as part of the workbook. "

I can't find that option. Where should I look for it? Which menu?

"Ron Rosenfeld" wrote:

On Thu, 16 Jul 2009 12:04:04 -0700, Eric_NY

wrote:

Ron - Thanks. I'm going to try the Longre morefunc add-in.

If I use the add-in functions and then send the sheet to someone by
email,
will the formulas still work? Or does the recipient also have to
install the
add-in separately on his own machine?

Thanks for your help.

Ordinarily no. They would have to install it themselves. However,
with the
morefunc add-in there is an option, I believe it is a menu option, to
install
the add-in as part of the workbook. If you do that, it will then be
usable by
the recipient with no particular effort on his part.

One caution concerning the add-in -- it will not work on strings that
are
longer than 255 characters. This, apparently is an .xll limitation,
and there
is no good way around it within the add-in.

By the way, if you should use the UDF approach, the UDF should be
embedded
within the workbook, so its use should be transparent to your user.
But there
are a lot of other useful functions in morefunc.
--ron





  #22  
Old July 18th, 2009, 12:09 PM posted to microsoft.public.excel.misc
Ron Rosenfeld
external usenet poster
 
Posts: 3,719
Default Need formula to extract a numeric value from a free-format tex

On Sat, 18 Jul 2009 02:26:01 -0700, Lori
wrote:

After further investigations, i think i've got a simplified version:

=LOOKUP(1E8,--(MID(A4&".",COLUMN(A:IV),7)&" 0/1"))

which returns the last 7 digit integer and errors if none is found.
[&" 0/1" ensures only integers are returned; &"e0" would return decimals too]


It also returns seven digit numbers that are part of other strings, including
longer strings of digits.
--ron
  #23  
Old July 18th, 2009, 12:40 PM posted to microsoft.public.excel.misc
Lori
external usenet poster
 
Posts: 673
Default Need formula to extract a numeric value from a free-format tex

I'm not sure what you mean by "numbers that are part of other strings".
I thought that's what was wanted but i'm probably missing something.

As Rick states the requirements are not clear enough on this.

"Ron Rosenfeld" wrote:

On Sat, 18 Jul 2009 02:26:01 -0700, Lori
wrote:

After further investigations, i think i've got a simplified version:

=LOOKUP(1E8,--(MID(A4&".",COLUMN(A:IV),7)&" 0/1"))

which returns the last 7 digit integer and errors if none is found.
[&" 0/1" ensures only integers are returned; &"e0" would return decimals too]


It also returns seven digit numbers that are part of other strings, including
longer strings of digits.
--ron

  #24  
Old July 18th, 2009, 01:25 PM posted to microsoft.public.excel.misc
Ron Rosenfeld
external usenet poster
 
Posts: 3,719
Default Need formula to extract a numeric value from a free-format tex

On Sat, 18 Jul 2009 04:40:01 -0700, Lori
wrote:

I'm not sure what you mean by "numbers that are part of other strings".
I thought that's what was wanted but i'm probably missing something.


Sorry. That statement was not clear.

What I meant is that your routine seems to return seven digits from substrings
that are longer.

NH1234567890 -- 4567890
45678901234 -- 8901234

I interpreted the OP's requirements to indicate that he wanted to extract the
first seven digit WORD; whereas your routines extract the first (or last) seven
consecutive digits, even if they are part of another word.

In the regex I presented, WORD is defined as a seven digit string of digits
surrounded by a non-word character. A non-word character is anything except a
digit, letter or underscore.

So, mine is somewhat flawed in that it would return seven digits if they were
prepended by, let us say, an asterisk or ampersand; but it would return
**MISSING** in the above instances.

But given &1234567*, mine would also return the seven digits. This could be
taken care of, if necessary, by defining word a bit differently.
--ron
  #25  
Old July 18th, 2009, 03:54 PM posted to microsoft.public.excel.misc
Rick Rothstein[_2_]
external usenet poster
 
Posts: 2,013
Default Need formula to extract a numeric value from a free-format text

I know you asked for a formula, but would a UDF (user defined function) be
acceptable (it would require allowing macros to run)? If so, press Alt+F11
to go into the VB editor and, once there, add a Module (Insert/Module from
its menu bar). Next copy/paste the following into the code window that
opened up...

Function First7DigitNumber(S As String) As String
Dim X As Long
For X = 1 To Len(S)
If Mid(" " & S & " ", X, 9) Like "[!0-9]#######[!0-9]" Then
First7DigitNumber = Mid(S, X, 7)
End If
Next
End Function

Now, go back to your worksheet and use this formula in whatever cell you
want (changing the A1 reference to the cell address containing your text)...

=First7DigitNumber(A1)

This UDF finds the first "isolated" 7 digit number (that is, a 7 digit
number at the beginning or end of the text or, if interior to the text, with
non-digit characters in front and behind it).

--
Rick (MVP - Excel)


"Eric_NY" wrote in message
...
I have a column containing text values like "User requested authority of
emergency ID for reason NHUSER23 Restore of object to library LEVEL2
under remedy 1074317. 06/04/09 17:46 QPGMR".

Some of the cells contain a 7-digit number and others don't. The 7-digit
number does not start at a fixed location in the text.

Is it possible to write a formula which looks for the 7-digit number, and
returns the number if present, and another value (such as 0 or
"*MISSING*")
if not?

I'm using Excel 2003.

Thanks.


  #26  
Old July 18th, 2009, 04:38 PM posted to microsoft.public.excel.misc
Ron Rosenfeld
external usenet poster
 
Posts: 3,719
Default Need formula to extract a numeric value from a free-format text

On Sat, 18 Jul 2009 10:54:43 -0400, "Rick Rothstein"
wrote:

Function First7DigitNumber(S As String) As String
Dim X As Long
For X = 1 To Len(S)
If Mid(" " & S & " ", X, 9) Like "[!0-9]#######[!0-9]" Then
First7DigitNumber = Mid(S, X, 7)
End If
Next
End Function


I note that given the following modification of the OP's test string:

"User requested authority of emergency ID for reason NHUSER1234567 Restore
of object to library LEVEL2 under remedy 61074317. 06/04/09 17:46 QPGMR"

your routine returns 1234567 whereas my UDF returns "MISSING" since there are
no seven digit words.

(Lori's formulas return 1074317)

--ron
  #27  
Old July 18th, 2009, 04:58 PM posted to microsoft.public.excel.misc
Rick Rothstein[_2_]
external usenet poster
 
Posts: 2,013
Default Need formula to extract a numeric value from a free-format text

I am still not sure if the OP wanted the 7-digit number to stand alone (as a
"word") or not, so I just went for the first isolated 7 digits in a row...

Function First7DigitNumber(S As String) As String
Dim X As Long
For X = 1 To Len(S)
If Mid(" " & S & " ", X, 9) Like "[!0-9]#######[!0-9]" Then
First7DigitNumber = Mid(S, X, 7)
Exit Function
End If
Next
First7DigitNumber = "*MISSING*"
End Function

--
Rick (MVP - Excel)


"Ron Rosenfeld" wrote in message
...
On Sat, 18 Jul 2009 10:54:43 -0400, "Rick Rothstein"
wrote:

Function First7DigitNumber(S As String) As String
Dim X As Long
For X = 1 To Len(S)
If Mid(" " & S & " ", X, 9) Like "[!0-9]#######[!0-9]" Then
First7DigitNumber = Mid(S, X, 7)
End If
Next
End Function


I note that given the following modification of the OP's test string:

"User requested authority of emergency ID for reason NHUSER1234567
Restore
of object to library LEVEL2 under remedy 61074317. 06/04/09 17:46 QPGMR"

your routine returns 1234567 whereas my UDF returns "MISSING" since there
are
no seven digit words.

(Lori's formulas return 1074317)

--ron


  #28  
Old July 18th, 2009, 05:03 PM posted to microsoft.public.excel.misc
Rick Rothstein[_2_]
external usenet poster
 
Posts: 2,013
Default Need formula to extract a numeric value from a free-format text

My last message had the wrong opening sentence (it was meant for another
message I was working on). This is what I meant to post...

The function I posted does not work as I had indicated... it finds the last
7-digit number, not the first. Here is the corrected code (plus I added the
missing *MISSING* indicator)...

Function First7DigitNumber(S As String) As String
Dim X As Long
For X = 1 To Len(S)
If Mid(" " & S & " ", X, 9) Like "[!0-9]#######[!0-9]" Then
First7DigitNumber = Mid(S, X, 7)
Exit Function
End If
Next
First7DigitNumber = "*MISSING*"
End Function

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
I am still not sure if the OP wanted the 7-digit number to stand alone (as
a "word") or not, so I just went for the first isolated 7 digits in a
row...

Function First7DigitNumber(S As String) As String
Dim X As Long
For X = 1 To Len(S)
If Mid(" " & S & " ", X, 9) Like "[!0-9]#######[!0-9]" Then
First7DigitNumber = Mid(S, X, 7)
Exit Function
End If
Next
First7DigitNumber = "*MISSING*"
End Function

--
Rick (MVP - Excel)


"Ron Rosenfeld" wrote in message
...
On Sat, 18 Jul 2009 10:54:43 -0400, "Rick Rothstein"
wrote:

Function First7DigitNumber(S As String) As String
Dim X As Long
For X = 1 To Len(S)
If Mid(" " & S & " ", X, 9) Like "[!0-9]#######[!0-9]" Then
First7DigitNumber = Mid(S, X, 7)
End If
Next
End Function


I note that given the following modification of the OP's test string:

"User requested authority of emergency ID for reason NHUSER1234567
Restore
of object to library LEVEL2 under remedy 61074317. 06/04/09 17:46 QPGMR"

your routine returns 1234567 whereas my UDF returns "MISSING" since
there are
no seven digit words.

(Lori's formulas return 1074317)

--ron



  #29  
Old July 18th, 2009, 05:06 PM posted to microsoft.public.excel.misc
Rick Rothstein[_2_]
external usenet poster
 
Posts: 2,013
Default Need formula to extract a numeric value from a free-format text

I am still not sure if the OP wanted the 7-digit number to stand alone (as a
"word") or not, so I just went for the first isolated 7 digits in a row
whether imbedded in other text or not. The reason I thought that is because
of the "dot" that followed the 7-digit number in the OP's posted example
text. In thinking about it, I'm guessing you took that to be a period at the
end of a sentence.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
I know you asked for a formula, but would a UDF (user defined function) be
acceptable (it would require allowing macros to run)? If so, press Alt+F11
to go into the VB editor and, once there, add a Module (Insert/Module from
its menu bar). Next copy/paste the following into the code window that
opened up...

Function First7DigitNumber(S As String) As String
Dim X As Long
For X = 1 To Len(S)
If Mid(" " & S & " ", X, 9) Like "[!0-9]#######[!0-9]" Then
First7DigitNumber = Mid(S, X, 7)
End If
Next
End Function

Now, go back to your worksheet and use this formula in whatever cell you
want (changing the A1 reference to the cell address containing your
text)...

=First7DigitNumber(A1)

This UDF finds the first "isolated" 7 digit number (that is, a 7 digit
number at the beginning or end of the text or, if interior to the text,
with non-digit characters in front and behind it).

--
Rick (MVP - Excel)


"Eric_NY" wrote in message
...
I have a column containing text values like "User requested authority of
emergency ID for reason NHUSER23 Restore of object to library LEVEL2
under remedy 1074317. 06/04/09 17:46 QPGMR".

Some of the cells contain a 7-digit number and others don't. The 7-digit
number does not start at a fixed location in the text.

Is it possible to write a formula which looks for the 7-digit number, and
returns the number if present, and another value (such as 0 or
"*MISSING*")
if not?

I'm using Excel 2003.

Thanks.



  #30  
Old July 18th, 2009, 05:10 PM posted to microsoft.public.excel.misc
Rick Rothstein[_2_]
external usenet poster
 
Posts: 2,013
Default Need formula to extract a numeric value from a free-format text

I am still not sure if the OP wanted the 7-digit number to stand alone (as a
"word") or not, so I just went for the first isolated 7 digits in a row
whether imbedded in other text or not. The reason I thought that is because
of the "dot" that followed the 7-digit number in the OP's posted example
text. In thinking about it, I'm guessing you took that to be a period at the
end of a sentence.

With that said, I made a mistake in my original function and left out the
*MISSING* indicator. I just posted a corrected function against my original
message for the function.

--
Rick (MVP - Excel)


--
Rick (MVP - Excel)


"Ron Rosenfeld" wrote in message
...
On Sat, 18 Jul 2009 10:54:43 -0400, "Rick Rothstein"
wrote:

Function First7DigitNumber(S As String) As String
Dim X As Long
For X = 1 To Len(S)
If Mid(" " & S & " ", X, 9) Like "[!0-9]#######[!0-9]" Then
First7DigitNumber = Mid(S, X, 7)
End If
Next
End Function


I note that given the following modification of the OP's test string:

"User requested authority of emergency ID for reason NHUSER1234567
Restore
of object to library LEVEL2 under remedy 61074317. 06/04/09 17:46 QPGMR"

your routine returns 1234567 whereas my UDF returns "MISSING" since there
are
no seven digit words.

(Lori's formulas return 1074317)

--ron


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:19 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.