I guess I should mention... if the number can contain a decimal point and

the region settings are for the decimal point to be a comma, or if the

number contains "thousands separators", then the function, as posted, won't

work. This one will for either decimal point setting (provided the number

still doesn't have thousands separators in it)...

Function ExtractNumber(rCell As Range) As Double

Dim X As Long

For X = 1 To Len(rCell.Value)

If Mid$(rCell.Value, X, 1) Like "*[0-9.]" Then

ExtractNumber = Val(Replace(Mid$(rCell.Value, X), ",", "."))

Exit For

End If

Next

End Function

Rick

Rick Rothstein (MVP - VB) said:

Here is my offering...

Function ExtractNumber(rCell As Range) As Double

Dim X As Long

For X = 1 To Len(rCell.Value)

If Mid$(rCell.Value, X, 1) Like "*[0-9.]" Then

ExtractNumber = Val(Mid$(rCell.Value, X))

Exit For

End If

Next

End Function

Rick

caroline said:

hello,

I am using the following code to extract numbers from a string

http://www.ozgrid.com/VBA/ExtractNum.htm
however when I have strings like WS123ABC45cft, I would like to extract

only

the first set of number"123". The code provided extracts "12345"

Do you know how to correct the code?

Please note that the number of numbers and letters are variables so I

cannot

use LEFT or RIGHT.

thanks