On Tue, 12 Apr 2011 01:23:40 -0700, "Robert Crandal" wrote:

Suppose I have the following string:

"The street is 1501 S. Oak Lane, Apt #228"

I need code that would extract the first numeric
substring (starting from the LEFT). Therefore, I
would need to extract "1501 from this string.

Or, I might have a string that looks like this:

"cat. dog mouse 12 8 112 house tree"

For that string, I would need to extract the "12"
only, because it is the first numeric substring or token.

Does anyone have any code samples that can extract
the substring that I describe above?? Please note
that any number of whitespace (space or tab) characters
may separate the string elements.

Thank you!

Robert Crandal


For a worksheet formula, you could use:

=LOOKUP(1E+307,--MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&"1,2,3,4, 5,6,7,8,9,0")),ROW($1:$99)))

which will return an error if there are no digits in the string.

For VBA code, one way with regular expressions, and, for this exercise, assuming that all of the values are unsigned digit strings, and this returns the value as a string, and returns nothing if there are no digits:

(returning the result as a string allows retention of leading zero's)

===================
Option Explicit
Function ExtrDigits(s As String) As String
Dim re As Object, mc As Object
Const sPat As String = "\d+"

Set re = CreateObject("vbscript.regexp")
re.Global = False
re.Pattern = sPat

If re.test(s) Then
Set mc = re.Execute(s)
ExtrDigits = mc(0)
End If

End Function
==========================

If you need to also handle floating point numbers, signed numbers or fractions, the Pattern can be changed to accomodate.

If you need to return more than the first digit, the code can be altered easily to handle this.