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