tag:blogger.com,1999:blog-2509167754737584590.post595254743607698695..comments2020-01-22T14:19:52.733+05:30Comments on Yogesh Gupta's Excel Tips: Excel Macros : Text to Numbers VBAYogesh Guptahttp://www.blogger.com/profile/08246342663404873564noreply@blogger.comBlogger8125tag:blogger.com,1999:blog-2509167754737584590.post-9943034315820266162009-12-24T11:17:23.122+05:302009-12-24T11:17:23.122+05:30Hey guys thanks for this rich source of knowledge ...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.<br /><br />Happy New Year to you all.<br /><br />Ninad.Ninadhttps://www.blogger.com/profile/09010642049136695229noreply@blogger.comtag:blogger.com,1999:blog-2509167754737584590.post-65628194479440405042009-12-05T13:46:31.622+05:302009-12-05T13:46:31.622+05:30Hello Rick
You always add value to my posts, that...Hello Rick<br /><br />You always add value to my posts, that makes content richer for users of myblog.<br /><br />Thanks once again.<br /><br />Yogesh GuptaYogesh Guptahttps://www.blogger.com/profile/08246342663404873564noreply@blogger.comtag:blogger.com,1999:blog-2509167754737584590.post-59864986512626310442009-12-05T13:13:52.604+05:302009-12-05T13:13:52.604+05:30And here is an even shorter UDF...
Function Numbe...And here is an even shorter UDF...<br /><br />Function Number(ByVal CurrString As String)<br /> Dim X As Long<br /> For X = 1 To Len(CurrString)<br /> Number = val(Mid(CurrString, X))<br /> If IsNumeric(Number) And Number > 0 Then Exit For<br /> Next<br />End Function<br /><br />Rick Rothstein (MVP - Excel)Rickhttps://www.blogger.com/profile/07572190007880814311noreply@blogger.comtag:blogger.com,1999:blog-2509167754737584590.post-39772665912857889722009-12-05T13:05:03.794+05:302009-12-05T13:05:03.794+05:30Here is a slightly shorter UDF that does what your...Here is a slightly shorter UDF that does what your UDF does...<br /><br />Function Number(ByVal CurrString As String)<br /> Dim X As Long<br /> For X = 1 To Len(CurrString)<br /> If IsNumeric(Mid(CurrString, X, 1)) Then<br /> Number = Val(Mid(CurrString, X))<br /> Exit For<br /> End If<br /> Next<br />End Function<br /><br />Rick Rothstein (MVP - Excel)Rickhttps://www.blogger.com/profile/07572190007880814311noreply@blogger.comtag:blogger.com,1999:blog-2509167754737584590.post-8628682362500958812009-12-05T12:59:19.497+05:302009-12-05T12:59:19.497+05:30Actually, to fully duplicate your UDF, the workshe...Actually, to fully duplicate your UDF, the worksheet formula needs to look like this...<br /><br />=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))))<br /><br />Rick Rothstein (MVP - Excel)Rickhttps://www.blogger.com/profile/07572190007880814311noreply@blogger.comtag:blogger.com,1999:blog-2509167754737584590.post-46004028255047465102009-12-05T12:32:27.697+05:302009-12-05T12:32:27.697+05:30Okay, in looking at your UDF closer, I see that th...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...<br /><br />=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)))<br /><br />Rick Rothstein (MVP - Excel)Rickhttps://www.blogger.com/profile/07572190007880814311noreply@blogger.comtag:blogger.com,1999:blog-2509167754737584590.post-82118874212673572782009-12-05T12:30:08.112+05:302009-12-05T12:30:08.112+05:30Hello Rick
Thanks for pointing out. Actualy this ...Hello Rick<br /><br />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. <br /><br />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.<br /><br />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.<br /><br />Thanks<br />Yogesh GuptaYogesh Guptahttps://www.blogger.com/profile/08246342663404873564noreply@blogger.comtag:blogger.com,1999:blog-2509167754737584590.post-14789946330887883202009-12-05T12:12:20.525+05:302009-12-05T12:12:20.525+05:30I'm a little confused as to the rule governing...I'm a little confused as to the rule governing your UDF. Why for this piece of data...<br /><br />10005 0000 A1<br /><br />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.<br /><br />Rick Rothstein (MVP - Excel)Rickhttps://www.blogger.com/profile/07572190007880814311noreply@blogger.com