Find the first numeric substring

[Robert Crandal]

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

 

[Rick Rothstein]

You didn't tell us whether the numbers you are trying to find could possibly
be floating point values or not. The following will work for whole numbers
or floating point numbers where the decimal point is a "dot" ...

Dim X As Long, FirstNumber As Double, Text As String
Text = "cat. dog mouse 12 8 112 house tree"
For X = 1 To Len(Text)
If IsNumeric(Mid(Text, X, 1)) Then
FirstNumber = Val(Mid(Replace(Text, " ", "Z"), X))
Exit For
End If
Next

If your decimal point is a "comma", then the following code should work for
you...

Dim X As Long, FirstNumber As Double, Text As String
Text = "cat. dog mouse 12 8 112 house tree"
For X = 1 To Len(Text)
If IsNumeric(Mid(Text, X, 1)) Then
FirstNumber = Val(Mid(Replace(Replace(Text, ",", "."), " ", "Z"), X))
Exit For
End If
Next

Rick Rothstein (MVP - Excel)



Robert Crandal" wrote in message

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

 

[Robert Crandal]

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.

So, for example:

1) "cat dog11 23 tree carrot"
- should return "23"

2) "rock paper scissors 12b 100 pencil"
- should return "100"

I should have been more specific earlier, but I'm just now
realizing exactly what I need. Also, you can safely
assume that all my data will contain at least one number
substring that matches the above criteria or specs; there
will never be any data that is missing numbers padded by
whitespace characters.

Okay, I'll play with you code now and see if it still works
for my requirements.

Thanks Rick

 

[Ron Rosenfeld]

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.

 

[Ron Rosenfeld]

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.

So, for example:

1) "cat dog11 23 tree carrot"
- should return "23"

2) "rock paper scissors 12b 100 pencil"
- should return "100"

I should have been more specific earlier, but I'm just now
realizing exactly what I need. Also, you can safely
assume that all my data will contain at least one number
substring that matches the above criteria or specs; there
will never be any data that is missing numbers padded by
whitespace characters.

Okay, I'll play with you code now and see if it still works
for my requirements.

Thanks Rick

Given those parameters, this modification of my original code should work:

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

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

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

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