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
I'm a little confused as to the rule governing your UDF. Why for this piece of data...
ReplyDelete10005 0000 A1
do you extract the first two numbers (and concatenate them together while ignoring the third number at the end? At first I thought your rule was to extract the first number you come to, but in this case you extracted the first two numbers you came to.
Rick Rothstein (MVP - Excel)
Hello Rick
ReplyDeleteThanks for pointing out. Actualy this UDF extracts first number until it is seprated by a non numeric character, however this ignores any space separating the number.
The logic in this UDF is to remove all non numeric character in the begining of text. Once the string has numeric value at start I am using Val(String) statement to extract number. This ignores any space seprating the number.
Other way could be start building new string after you hit numeric character till you encounter a space or non numeric character. That basis I will get only first number.
Thanks
Yogesh Gupta
Okay, in looking at your UDF closer, I see that the rule is to get the first number you come to... I didn't remember that the Val function ignores spaces in the middle of numbers. Here is a worksheet formula that does the same thing as your UDF...
ReplyDelete=LOOKUP(9.9E+307,--LEFT(MID(SUBSTITUTE(A1," ",""),MIN(FIND({0,1,2,3,4,5,6,7,8,9},SUBSTITUTE(A1," ","")&"0123456789")),999),ROW($1:$99)))
Rick Rothstein (MVP - Excel)
Actually, to fully duplicate your UDF, the worksheet formula needs to look like this...
ReplyDelete=IF(A1="",0,LOOKUP(9.9E+307,--LEFT(MID(SUBSTITUTE(A1," ",""),MIN(FIND({0,1,2,3,4,5,6,7,8,9},SUBSTITUTE(A1," ","")&"0123456789")),999),ROW($1:$99))))
Rick Rothstein (MVP - Excel)
Here is a slightly shorter UDF that does what your UDF does...
ReplyDeleteFunction Number(ByVal CurrString As String)
Dim X As Long
For X = 1 To Len(CurrString)
If IsNumeric(Mid(CurrString, X, 1)) Then
Number = Val(Mid(CurrString, X))
Exit For
End If
Next
End Function
Rick Rothstein (MVP - Excel)
And here is an even shorter UDF...
ReplyDeleteFunction Number(ByVal CurrString As String)
Dim X As Long
For X = 1 To Len(CurrString)
Number = val(Mid(CurrString, X))
If IsNumeric(Number) And Number > 0 Then Exit For
Next
End Function
Rick Rothstein (MVP - Excel)
Hello Rick
ReplyDeleteYou always add value to my posts, that makes content richer for users of myblog.
Thanks once again.
Yogesh Gupta
Hey guys thanks for this rich source of knowledge sharing. I am familiar with Access, but in my current environment, XL is more widely used. And I am learning.
ReplyDeleteHappy New Year to you all.
Ninad.