Separate text from numbers

how many are you likely to need?
here is a (not very efficient udf )that will extract all numbers into one cell .then copy paste special values then split with text to columns
use as =remove_text(a1) and drag down

Function Remove_text(Rng As String) As String
Dim Tmp As String
Dim i As Integer
Dim x As Integer


Tmp = Rng
For i = 1 To 47
Tmp = Application.Substitute(Tmp, Chr(i), " ")
Next i
For x = 58 To 255
Tmp = Application.Substitute(Tmp, Chr(x), " ")

Next x
Tmp = Application.Trim(Tmp)

Remove_text = Tmp
End Function

FWIW - if you opted for UDF - Regular Expressions may be worth considering...

Function NumberExtract(rngString As Range, Optional lngInstance As Long = 1) As Variant
Dim RegExp As Object, RegExpMatch As Object
On Error Resume Next
Set RegExp = CreateObject("vbscript.regexp")
With RegExp
    .Global = True
    .IgnoreCase = True
    .Pattern = "[0-9]+"
End With
Set RegExpMatch = RegExp.Execute(rngString)
If lngInstance > RegExpMatch.Count Then
    NumberExtract = ""
Else
    NumberExtract = RegExpMatch(lngInstance - 1)
End If
Set RegExpMatch = Nothing
Set RegExp = Nothing
End Function

Assume string in A1, numbers to go in B1, C1 etc...

B1: =NUMBEREXTRACT($A1,COLUMNS($B1:B1))
copied across

The second variable is optional - ie should you only be looking to return a single numeric value (first) it could be omitted - in this case we increment the value to return 1st, 2nd, 3rd etc...

Not very effiicient though doing this with functions though IMO...a sub routine would be better.

EDIT:

if you want the output for finds to be numeric adjust the 2nd NumberExtract line to:

NumberExtract = Val(RegExpMatch(lngInstance - 1))