Parse Decimal Numbers From Text

minitman · Nov 6th 2006

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:

Code

Function ExtractNumber(rCell As Range)
Dim iCount As Integer, i As Integer
Dim sText As String
Dim lNum As String


''''''''''''''''''''''''''''''''''''''''''
'Written by OzGrid Business Applications
'www.ozgrid.com


'Extracts a number from a cell containing text and numbers.
''''''''''''''''''''''''''''''''''''''''''
sText = rCell


    For iCount = Len(sText) To 1 Step -1
        If IsNumeric(Mid(sText, iCount, 1)) Then
            i = i + 1
            lNum = Mid(sText, iCount, 1) & lNum
        End If


        If i = 1 Then lNum = CInt(Mid(lNum, 1, 1))
    Next iCount


ExtractNumber = CLng(lNum)


End Function

Display More

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

**Krishnakumar** · Nov 6th 2006

Re: Extractnumber Removes Decimal With Letters

Hi,

Download MOREFUNC Add-In and try,

=REGEX.SUBSTITUTE(A1,"[A-Za-z]","")+0

Also you could try,

=substitute(a1," grams","")+0

HTH

Rich_z · Nov 6th 2006

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

minitman · Nov 6th 2006

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.

**Krishnakumar** · Nov 6th 2006

Re: Extractnumber Removes Decimal With Letters

I think the morefunc add-in is much faster.

Also did you try the native solution. You can nest other 7 characters in the substitute function.

HTH

minitman · Nov 6th 2006

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

Dave Hawley · Nov 6th 2006

Re: Extractnumber Removes Decimal With Letters

Been meaning to replace that one. Anyway, use

Code

Function ExtractNumber(rCell As Range) As Double
    Dim iCount As Integer, i As Integer
    Dim sText As String
    Dim lNum As String
    Dim vVal
     
     ''''''''''''''''''''''''''''''''''''''''''
     'Written by OzGrid Business Applications
     'www.ozgrid.com
     
     'Extracts a number from a cell containing text and numbers.
     ''''''''''''''''''''''''''''''''''''''''''
    sText = rCell
     
    For iCount = Len(sText) To 1 Step -1
    vVal = Mid(sText, iCount, 1)
        If IsNumeric(vVal) Or vVal = "." Or vVal = "-" Then
            i = i + 1
            lNum = Mid(sText, iCount, 1) & lNum
        End If
         
        If i = 1 Then lNum = CDbl(Mid(lNum, 1, 1))
    Next iCount
     
    ExtractNumber = CDbl(lNum)
     
End Function

Display More

minitman · Nov 6th 2006

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

Dave Hawley · Nov 6th 2006

Re: Parse Decimal Numbers From Text

I've been meaning to fix that for over a year now! I needed the push

minitman · Nov 6th 2006

Re: Parse Decimal Numbers From Text

Glad to be of service....

brook1 · Nov 8th 2006

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

Dave Hawley · Nov 8th 2006

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.

Rich_z · Nov 9th 2006

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

Parse Decimal Numbers From Text

Participate now!

Share