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

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.

10. May I just say what a comfort to uncover someone who actually knows
what they are discussing on the net. You actually know how to bring a problem to light and make it important. do my homework for money More people should read this and understand this side of your story. I was surprised you’re not more popular given that you certainly have the gift.

11. Our USA essay experts have major degrees pay to do college homework in their field of specialization and provide the services for writing essay for a wide range of subjects.

12. School expositions are intended to be an individual story, so the gamble of somebody taking pay equity research paper work is most certainly lower than your regular influential or factious papers from school tasks.

13. Be that as it may, what number of captain america leather jacket avengers pants would it be advisable for you to claim? You ought to go for the gold 5 sets of denim pants, barring shorts. It is a decent number to meet your regular necessities, despite the fact that a lady own 7 sets overall, and men 6.

14. We have vast industry expertise, therefore we are completely aware of what is required to properly complete the taskCEMENT TREATED BASE service in texas of  Get in contact with us right away if you would like more information about our offerings and how we can help you get the best outcomes.

15. We promise you'll have a blast participating in our tournaments, which are made for competitive players of all skill levels. We have tournaments for games such as CS:GO, Dota 2,UAE esports in Dubai,404 esports in Dubai and League of Legends. To compete against the top players on the planet, sign up right away.