Home |
Search |
Today's Posts |
#23
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need formula to extract a numeric value from a free-format text
I found this definition of a word boundary in Regular Expressions...
"A word boundary represents the spot where a letter or number meets a space, apostrophe, a period, or anything else that isn't a letter or number" Given that, this modification of my function should do what your RegExp solution does... Function First7DigitNumber(S As String) As String Dim X As Long For X = 1 To Len(S) If Mid(" " & S & " ", X, 9) Like "[!a-zA-Z0-9]#######[!a-zA-Z0-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) |