Saturday, May 09, 2009

VBA for Excel

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:
=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9),1)),0),COUNT(1*MID(A1,ROW($1:$9),1)))

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)
Loop
GetNumber = Right(MyNumber, i - 1)
End Function

And in excel:
=VALUE(GetNumber(A1))
and off you go!

No comments: