Home |
Search |
Today's Posts |
#21
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find text in free-format text field | Excel Discussion (Misc queries) | |||
Extract just numeric part of mixed text/number entry? | Excel Worksheet Functions | |||
only extract numeric value from alpha numeric cell | Excel Discussion (Misc queries) | |||
Change number (in text format) to numeric format | Excel Discussion (Misc queries) | |||
Everytime I need to format cells or text, Excel 2003 takes a lot of time or free | Excel Discussion (Misc queries) |