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
|