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
