Parse Decimal Numbers From Text

  • Greetings,


    A while back I asked how to remove the letters from entries that have both numbers and letters. I as pointed to a UDF called "ExtractNumber".


    Here is the code for that UDF:

    It seemed to work great.


    Recently, I noticed that it has a bug that only shows up under one confition, if the number has a decimal in it. In the situation, the result is the number will come out as if there was no decimal (Eg.: "5.75 grams" comes out as "575" instead of "5.75").


    Is there any way to fix this bug?


    Any help is appreciated.


    -Minitman

  • Re: Extractnumber Removes Decimal With Letters


    Insert after the 'Sub' statement:


    Code
    Const c_number_punctuation as string = "."


    And replace


    Code
    If IsNumeric(Mid(sText, iCount, 1)) Then


    With


    Code
    If IsNumeric(Mid(sText, iCount, 1)) or Instr(c_Number_Punctuation, Mid(sText, iCount, 1)) > 0 Then


    To add extra characters to leave in the string, add them to the constant (for example the minus sign, the plus sign etc)


    Regards


    Rich

  • Re: Extractnumber Removes Decimal With Letters


    Thanks guys for the replies.


    Krishnakumar: Question, which route will be faster in a recalculation, your addin or the fixed UDF?


    Rich: Your solution is exactly what I was looking for, but now I am not sure if that is what I really need. I did have to change the CInt and the CLng to CDec to get the decimal to work. But that was the easy part, thanks for the fix.


    I am also having a problem with recalcs taking about 5 minutes each time and was wondering if this function could be a part of the problem (I have already cleared the unused areas of this workbook, that helped some). Or would an addin possibly be faster?


    Again I want thank both of you for replying as quckly as you did.


    -Minitman.

  • Re: Extractnumber Removes Decimal With Letters


    hey Krishnakumar,


    I don't have a need for nesting that I can see. This UDF is being used to remove not just one word (grams) but all forms of both mass and volume (metric & standard) to create a ratio of actual to suggested measurments and this ratio is then applied to each of the 12 nutrition values of the standard size to come up with the nutritional values of the actual size. I will look at the addin link you gave me tommorrow and see how it compares speedwise wit the UDF. The UDF is now working without any problems (except as noted in the earlier post - the speed issue)


    Again, thanks for this addin solution.


    -Minitman

  • Re: Extractnumber Removes Decimal With Letters


    Been meaning to replace that one. Anyway, use

  • Re: Parse Decimal Numbers From Text


    Hey Dave,


    Just tried it - Works great.


    Thanks for taking the time to fix it now instead of later.


    The fix that Rich gave works but yours looks more finished (the masters touch).


    I really do appreciate the work you put into this site and all of the help you give.


    -Minitman

  • Re: Parse Decimal Numbers From Text


    Dave,
    there may be times when '0's may be needed to be returned as '0's rather than eliminated, such as:


    0a0b1c2d0


    returns 120, but one may actually want 00120


    just a thought

  • Re: Parse Decimal Numbers From Text


    Thanks, but that can only be handled by a Custom Number format like 00000 so the zeros display. To actually have zeros in front of a number would have to be text.

  • Re: Parse Decimal Numbers From Text


    Quote from minitman

    The fix that Rich gave works but yours looks more finished (the masters touch).


    Huh! ;)


    As a general note if you look at this routine as a very useful tool that you can use over and again, by putting the strings you are searching for into a constant if you need to include more characters, you just add them to the constant. The actual code does not change. The other thing is, by removing the constant and changing references to it to a sub routine parameter you have a routine that can be used to remove all sorts of stuff from a string.


    By including them as clauses in the if statement this 'reusefulness' dissappears to a certain extent.


    Regards


    Rich

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!