Saturday, December 5, 2009

Excel Macros : Text to Numbers VBA

Some times you get data which has some numbers surrounded by lots of text or non text characters or space in between text and numbers. Position of the numbers within text is not same in each data line. In such situation you may not be able to apply standard Excel Formulas to extract number from text. In such scenario you will need to use VBA to Extract Number from String. Today I am sharing with you UDF to perform this function. Just for illustration following table will show you capability of this UDF.

Text Data
Extracted Numeric Value
xusdhd 10005000
10005000
AKSID0100050000 A1
100050000
IU EW KFID100050000 A 1
100050000
10005 0000 A1
100050000
01000 A1 B 12
1000


Above Numeric Values have been extracted using simple formula =Number(B3) , In this case text string is in Cell B3. This formula is not available in standard excel functions. To apply this formula You will need to copy this code to regular VBA module of your workbook

'****************************************************************
'* User Defined Function (UDF) to Get Numeric Value from String *
'****************************************************************
Function Number(ByVal CurrString As String)
Dim temp As String

temp = Left(CurrString, 1)
Do While Not IsNumeric(temp)
If Len(CurrString) <= 1 Then
Exit Function
Else
CurrString = Mid(CurrString, 2)
temp = Left(CurrString, 1)
End If
Loop
Number = Val(CurrString)
End Function


Download excel file with UDF for Text to Numbers