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
  #1  
Old July 16th, 2009, 07:22 PM posted to microsoft.public.excel.misc
Eric_NY
external usenet poster
 
Posts: 88
Default Need formula to extract a numeric value from a free-format text

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.
  #2  
Old July 16th, 2009, 07:25 PM posted to microsoft.public.excel.misc
Bob Phillips[_2_]
external usenet poster
 
Posts: 1,562
Default Need formula to extract a numeric value from a free-format text

Is there any hard and fast rule that governs where the number might appear,
such as before the first full stop?

--
__________________________________
HTH

Bob

"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.



  #4  
Old July 16th, 2009, 07:35 PM posted to microsoft.public.excel.misc
joeu2004
external usenet poster
 
Posts: 1,748
Default Need formula to extract a numeric value from a free-format text

If the word "remedy" and a single space always precede the number, and if
the number is truly always 7 digits, you could use the following (A1
contains the original text):

=IF(ISNUMBER(FIND("remedy",A1)), MID(A1,FIND("remedy",A1)+7,7), "missing")

Otherwise, it probably will be easier to write a user-defined function in
VB. However, the rules for finding the desired number must be precise. For
example, is there always only one 7-digit number preceded by a space? Or is
there always specific set of words ("remedy" and others) preceding the
number?

Basically, how would a human distinguish and find the number if he had to
scan the text left to right or right to left character by character or word
by word, where a "word" is a sequence of non-space characters.


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

"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.


  #5  
Old July 16th, 2009, 07:38 PM posted to microsoft.public.excel.misc
Eric_NY
external usenet poster
 
Posts: 88
Default Need formula to extract a numeric value from a free-format tex

No, none that I can see. This is free-format input by the user, and as much
as we'd like to insist that the user use a prescribed format, we can't be
sure that they always will. I just need a way to look for a 7-digit number
anywhere within the text.

Thanks for any suggestions you can provide.

"Bob Phillips" wrote:

Is there any hard and fast rule that governs where the number might appear,
such as before the first full stop?

--
__________________________________
HTH

Bob

"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.




  #7  
Old July 16th, 2009, 07:44 PM posted to microsoft.public.excel.misc
Eric_NY
external usenet poster
 
Posts: 88
Default Need formula to extract a numeric value from a free-format tex

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.)

Maybe it's time for me finally to force myself to learn VB. I'm a former
programmer, but never learned VB.

Thanks for your suggestions.

"JoeU2004" wrote:

If the word "remedy" and a single space always precede the number, and if
the number is truly always 7 digits, you could use the following (A1
contains the original text):

=IF(ISNUMBER(FIND("remedy",A1)), MID(A1,FIND("remedy",A1)+7,7), "missing")

Otherwise, it probably will be easier to write a user-defined function in
VB. However, the rules for finding the desired number must be precise. For
example, is there always only one 7-digit number preceded by a space? Or is
there always specific set of words ("remedy" and others) preceding the
number?

Basically, how would a human distinguish and find the number if he had to
scan the text left to right or right to left character by character or word
by word, where a "word" is a sequence of non-space characters.


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

"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.



  #8  
Old July 16th, 2009, 07:50 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 Thu, 16 Jul 2009 11:22:01 -0700, Eric_NY
wrote:

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.


Easy to do using Regular Expressions.

The Regular Expression (Regex) would be "\b\d{7}\b" which translates into "find
a seven digit expression that is surrounded by word boundaries. You need to
assert the word boundary to avoid picking up 7 digits of a longer number that
might be there.

This can be implemented in several ways.

1. Download and install Longre's free morefunc.xll add-in (use Google to find a
functioning download site). Then use the formula:

=IF(REGEX.MID(A1,"\b\d{7}\b")="","*MISSING*",REGEX .MID(A1,"\b\d{7}\b"))

2. Write a short UDF using VBA.

To enter this User Defined Function (UDF), alt-F11 opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=Seven(A1)

in some cell.

================================
Option Explicit
Function Seven(s As String) As String
Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.Pattern = "\b\d{7}\b"
If re.test(s) = True Then
Set mc = re.Execute(s)
Seven = mc(0)
Else
Seven = "*MISSING*"
End If
End Function
=================================
--ron
  #9  
Old July 16th, 2009, 08:04 PM posted to microsoft.public.excel.misc
Eric_NY
external usenet poster
 
Posts: 88
Default Need formula to extract a numeric value from a free-format tex

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.

"Ron Rosenfeld" wrote:

On Thu, 16 Jul 2009 11:22:01 -0700, Eric_NY
wrote:

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.


Easy to do using Regular Expressions.

The Regular Expression (Regex) would be "\b\d{7}\b" which translates into "find
a seven digit expression that is surrounded by word boundaries. You need to
assert the word boundary to avoid picking up 7 digits of a longer number that
might be there.

This can be implemented in several ways.

1. Download and install Longre's free morefunc.xll add-in (use Google to find a
functioning download site). Then use the formula:

=IF(REGEX.MID(A1,"\b\d{7}\b")="","*MISSING*",REGEX .MID(A1,"\b\d{7}\b"))

2. Write a short UDF using VBA.

To enter this User Defined Function (UDF), alt-F11 opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=Seven(A1)

in some cell.

================================
Option Explicit
Function Seven(s As String) As String
Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.Pattern = "\b\d{7}\b"
If re.test(s) = True Then
Set mc = re.Execute(s)
Seven = mc(0)
Else
Seven = "*MISSING*"
End If
End Function
=================================
--ron

  #10  
Old July 16th, 2009, 08:26 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 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
 




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 02:37 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.