Formula to separate numbers from text

there will be other ways, no doubt more elegant...

=--LEFT(A1,SUMPRODUCT(--(ISNUMBER(--MID(SUBSTITUTE(SUBSTITUTE(A1,".",0),",",0),ROW(INDIRECT("1:"&LEN(A1))),1)))))

where A1 holds string... note in the above I've had to make the assumption that "." & "," are valid numerical delimiters (eg 42.5 or 1,002 etc...)

If it's possible that you would have additional numbers beyond the value of interest (eg 42.5g 10a) you would need to adopt a different approach (where you look for the first text character)

A user-defined function can do it. Insert this new function into your sheet in the same place regular macros go:

Function CleanAll(txt As String) As String
With CreateObject("VBScript.RegExp")
'enter all characters to be KEPT into the pattern
    .Pattern = "[^0-9.]"
    .Global = True
    .IgnoreCase = True
    CleanAll = Application.Trim(.Replace(txt, ""))
End With
End Function

Now this formula will leave only numbers and periods:

=CleanAll(A1)

==========
Alt-F11 to open VBEditor
Insert > Module
Paste in code
Alt-Q to exit VBEditor
Save sheet.