Find the first numeric substring
Ooops, my mistake about that. Um, all my data
samples ONLY contain whole numbers or integer
substrings, so there will never be any floating
point numbers.
To be even more precise, I am only looking for
an ALL numeric substring that has at least one
whitespace character on the left and at least
one whitespace character on the right.
Hmm! That changes everything. Okay, here is a function that will do this (it
can be used as a UDF, user defined function, if needed)...
Function GetNumber(ByVal Text As String) As Double
Dim X As Long, Temp As String
Text = Replace(Text, Chr(160), " ")
For X = 1 To Len(Text)
Temp = Mid(Text, X)
If Temp Like " #*" Then
Temp = Left(LTrim(Temp), InStr(LTrim(Temp), " ") - 1)
If Not Temp Like "*[!0-9]*" Then Exit For
End If
Next
GetNumber = Temp
End Function
Rick Rothstein (MVP - Excel)
|