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


"Rick Rothstein" wrote in message
...
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)