Standard Module Code:
Public Function OnlyNumbers(Cel As Range) As Long
'Will not work with decimals
'Will only Return first set of numbers. Ie 123ABC456 will return 123
'For Help, see: http://www.vbaexpress.com/forum/showthread.php?61763
Dim Str as String
Dim Num As String
Str = Cel.text
Do while Len(Str) >= 1
If IsNumeric Left(Str, 1) then Num = Num & Left(Str, 1)
If (Len(Num) > 0) And (Not IsNumeric(Left(Str, 1))) then Exit Do
If Len(Str) = 1 Then Exit Do
Str = Mid(Str, 2)
Loop
OnlyNumbers = CLng(Num)
End Function
B2 Formula = "=OnlyNumbers(A2)". Fill down