Summing brackets, when you have multiple brackets per cell

[tmiller1002]

Hey all,

I have a table of data that looks something like:

[ Part Number ] [Quantity] [ (2) 101 (3) 111 (4) 181 ]
[ Part Number ] [Quantity] [ (100) 199 (29) 111 ]

What I need to do is find the summation of each cell's numbers in parenthesis...
I've tried a method I found on here, but it only returns the value of the FIRST parenthesis per cell, when I need a sum of all.

Thank you so much for any help and guidance, love this forum so far.
TM

[BoredWorker]

Hi TM,

First,
I does not fully understand what you want to achieve.

hence, it is better to put a simple excel file that contain before and after, the most simple way to understand.

Second, I guess what you want to achieve is split a cell to several cell, [ (2) 101 (3) 111 (4) 181 ] >> [(2)],[101],[(3)],....
then sum it all.

if this is you want, I only can suggest a long way,
highlight the column that need to split,
Go to Data Ribbon > text to column > choose delimited > check Space > Finish

then put in the sum formula.

[tmiller1002]

aaaaaa.PNG



Sorry, I might not have been very clear. This is what my table looks like. I am wanting the value under "Quantity" to be the sum of the numbers in parenthesis under "Orders".

I've used:

=IF(ISERROR(MID(A1,FIND("(",A1)+1,(FIND(")",A1))-(FIND("(",A1)+1))),0,(MID(A1,FIND("(",A1)+1,(FIND(")",A1))-(FIND("(",A1)+1)))+0)

But only get the value of the FIRST parenthesis.


Hope this makes more sense....

TM

[tim201110]

user defined function

Function bras(d As String) As Long
Dim i&, nn&, nk&

Do While nk <= Len(d)
i = i + 1
    nn = InStr(i, d, "(")
    If nn = 0 Then Exit Function
        nk = InStr(nn + 1, d, ")")
            bras = bras + CLng(Mid(d, nn + 1, nk - nn - 1))
            i = nk
Loop
End Function