Extracting Numbers from text

**Marcol** · 12-10-2012, 10:47 AM

The most versatile way is to use VBa.

Try this UDF

Function SumCell(rng As Range)
    Dim strtmp As String
    Dim n As Long
    
    For n = 1 To Len(rng)
        If Asc(Mid(rng, n, 1)) >= 48 And Asc(Mid(rng, n, 1)) <= 57 Then
            strtmp = strtmp & Mid(rng, n, 1)
        Else
            strtmp = strtmp & " "
        End If
    Next
    strtmp = WorksheetFunction.Trim(strtmp)
    SumCell = Evaluate(Replace(strtmp, " ", "+"))
    
End Function

Use in worksheet

Formula:

=SumCell(A3)

**hoventim** · 12-10-2012, 06:06 PM

Marcol this works perfectly!

I will need to school myself on udf instalation and usage. Doesn't look to tuff for this newbie.

Thank you very much.

**mstubbs** · 12-10-2012, 06:32 PM

What about if your numbers are formatted in tenths and have ( ) around them? Such as:

ASDF(2.5); ASDF (3.6); ASDF (5.8)

Any way to get it to get you the total of the numbers in the same cell in the same format and only the numbers in between the ( )?

**Marcol** · 12-10-2012, 09:58 PM

Originally Posted by mstubbs

What about if your numbers are formatted in tenths and have ( ) around them? Such as:

ASDF(2.5); ASDF (3.6); ASDF (5.8)

Any way to get it to get you the total of the numbers in the same cell in the same format and only the numbers in between the ( )?

Assuming there are no "."s (dots) in the string, other than the decimal points.

Change the function to this ...

Function SumCell(rng As Range)
    Dim strtmp As String
    Dim n As Long
    
    For n = 1 To Len(rng)
        If Asc(Mid(rng, n, 1)) >= 48 And Asc(Mid(rng, n, 1)) <= 57 Or Asc(Mid(rng, n, 1)) = 46 Then
            strtmp = strtmp & Mid(rng, n, 1)
        Else
            strtmp = strtmp & " "
        End If
    Next
    strtmp = WorksheetFunction.Trim(strtmp)
    SumCell = Evaluate(Replace(strtmp, " ", "+"))
    
End Function

**mstubbs** · 12-11-2012, 01:23 AM

Thank you Marcol, I appreciate it. This worked somewhat, the only problem I have now is that I only want the numbers inside the ( ) to be totaled. If I add numbers outside of the ( ) it is still including them in the sum. Any ideas?

**Marcol** · 12-11-2012, 09:08 AM

Hi mstubbs

unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

I answered your first query in this thread because I felt it was relevant to the OPs' question.

You are now asking something quite different, and as the OP has marked the thread [SOLVED], better you start your own thread.

I suggest a title such as "Sum only numbers in brackets in a cell"

PM me when you have done this and I will post the UDF solution I have waiting for you.