Separate text from numbers

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))