## 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`