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:

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

And in excel:
and off you go!

No comments: