Extrapolating multiple occurences of data from cell using ( ) as an identifier.

**mstubbs** · 12-10-2012, 04:51 PM

Hello All-

I'm trying to figure out a way to pull the numbers out of a cell and total them up using ( ) to identify when the numbers occur. The cell contains something like this:

asdf(2.0); ASDF (3.0); ASDF (7.5);

Any ideas? I'm stuck...

**snax** · 12-10-2012, 07:58 PM

Are the values that you posted all in the same cell or in multiple cells? If they are in different cells then the below formula should work. You'll have to change the four instances of "A1" to the correct cell reference.

=MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1)

**mstubbs** · 12-10-2012, 08:25 PM

Thank you for the reply snax.

The values are all in the same cell. The problem I'm having is that I only want the numbers within the ( ) and I need to keep the format of the numbers to be in tenths.

**snax** · 12-11-2012, 11:09 AM

If you can do a text to columns with a semicolon as the delimiter and then use the formula above for each cell. If you can't do that then I believe the only other way to accomplish what you want done is through VBA.

**Marcol** · 12-11-2012, 12:59 PM

Probably the most versitile way to do this is with a UDF.

This will sum any amount of numbers in brackets.

Function SumCell(rng As Range)
    Dim strTemp As String
    Dim n As Long
    Dim switch As Boolean
    
    For n = 1 To Len(rng)
        If Not switch Then
            If Mid(rng, n, 1) = "(" Then
                strTemp = strTemp & Mid(rng, n, 1)
                switch = True
            Else
                strTemp = strTemp & " "
            End If
        Else
            If Mid(rng, n, 1) = ")" Then
                strTemp = strTemp & Mid(rng, n, 1)
                switch = False
            Else
                strTemp = strTemp & Mid(rng, n, 1)
            End If
        End If
    Next
    
    strTemp = WorksheetFunction.Trim(strTemp)
    
    SumCell = Evaluate(Replace(strTemp, " ", "+"))
End Function

Use in Excel

Formula:

=sumcell(A2)

See this workbook

[EDIT]
Workbook updated to prove your PM examples, seems fine to me ...

Added another UDF just for fun ...

**mstubbs** · 12-11-2012, 01:40 PM

This worked great, thanks Marcol!

Extrapolating multiple occurences of data from cell using ( ) as an identifier.

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Extrapolating multiple occurences of data from cell using ( ) as an identifier.

Re: Extrapolating multiple occurences of data from cell using ( ) as an identifier.

Re: Extrapolating multiple occurences of data from cell using ( ) as an identifier.

Re: Extrapolating multiple occurences of data from cell using ( ) as an identifier.

Re: Extrapolating multiple occurences of data from cell using ( ) as an identifier.

Re: Extrapolating multiple occurences of data from cell using ( ) as an identifier.

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions