Comments on Yogesh Gupta's Excel Tips: Excel Macros : Text to Numbers VBA

Hey guys thanks for this rich source of knowledge ...

2009-12-24T11:17:23.122+05:30

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.

Hello Rick You always add value to my posts, that...

2009-12-05T13:46:31.622+05:30

Hello Rick

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

Thanks once again.

Yogesh Gupta

And here is an even shorter UDF... Function Numbe...

2009-12-05T13:13:52.604+05:30

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)

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

2009-12-05T13:05:03.794+05:30

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)

Actually, to fully duplicate your UDF, the workshe...

2009-12-05T12:59:19.497+05:30

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)

Okay, in looking at your UDF closer, I see that th...

2009-12-05T12:32:27.697+05:30

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)

Hello Rick Thanks for pointing out. Actualy this ...

2009-12-05T12:30:08.112+05:30

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

I'm a little confused as to the rule governing...

2009-12-05T12:12:20.525+05:30

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)