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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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. |
#3
|
|||
|
|||
Need formula to extract a numeric value from a free-format text
Always the same number?? -- Don Guillett Microsoft MVP Excel SalesAid Software "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
|
|||
|
|||
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
|
|||
|
|||
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. |
#6
|
|||
|
|||
Need formula to extract a numeric value from a free-format tex
Unfortunately no.
"Don Guillett" wrote: Always the same number?? -- Don Guillett Microsoft MVP Excel SalesAid Software "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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|