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

9 comments:

  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)

    ReplyDelete
  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

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

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

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

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

    ReplyDelete
  7. Hello Rick

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

    Thanks once again.

    Yogesh Gupta

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

    Ninad.

    ReplyDelete
  9. Woderful content. I am regularly follow this blog. Thank you for updating such a good content. Please follow my Travel blog for more information about Eco Park in Kolkata.

    My other posts are:
    Victoria Memorial in Kolkata.
    Scuba Diving in Goa.
    Top visiting places in GOA.
    Calangute Beach in Goa.
    Candolim Beach in Goa.
    Baga Beach in Goa.

    ReplyDelete