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

1. I'm a little confused as to the rule governing your UDF. Why for this piece of data...

10005 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)

2. Hello Rick

Thanks 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

3. 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...

=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)

4. Actually, to fully duplicate your UDF, the worksheet formula needs to look like this...

=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)

5. Here is a slightly shorter UDF that does what your UDF does...

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

6. And here is an even shorter UDF...

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

7. Hello Rick

You always add value to my posts, that makes content richer for users of myblog.

Thanks once again.

Yogesh Gupta

8. 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.

Happy New Year to you all.  