Since there could be many non-numeric characters, the easiest option is to write a VBA UDF which will parse the numbers from the start of each string for you. This has been done lots of times before, so I did a quick search and found this function written by Harlan Grove:
Code:
Function DigitsFirstID(s As String) As String
'Harlan Grove, worksheet.functions, 2003-10-20
'extract first string of digits, based on
'-- http://google.com/groups?threadm=_RK...newsranger.com
Dim i As Long, j As Long, n As Long
n = Len(s)
i = 1
Do While i <= n And Mid(s, i, 1) Like "[!0-9]"
i = i + 1
Loop
j = i + 1
Do While j <= n And Mid(s, j, 1) Like "[0-9]"
j = j + 1
Loop
DigitsFirstID = Mid(s, i, j - i)
End Function
This needs to be copied and pasted into a standard code module in your workbook, and then you can use a formula such as this to extract the number:
=DigitsFirstID(A2)
I realise that VBA may be new to you so I have attached an example.