Extracting a number from text in excel is.. amazingly complex.
Here is the Microsoft way of extracting a number from a string in excel.
You know your in trouble in that they provide an 'algorithm' for doing this.
This is their way:
Logical right? or not..
Why on earth they didn't provide a "scanf" variant is beyond me.
Luckily, VB script to the rescue again: (to get the trailing number on the right)
Function GetNumber(MyEntry As String)
Do Until Not IsNumeric(MyNumber)
i = i + 1
MyNumber = Right(MyEntry, i)
GetNumber = Right(MyNumber, i - 1)
And in excel:
and off you go!