How to extract & add all the nos. from brackets in excel when there are such multiple instances-CodePudding

I am stuck with a data in excel having the above form and wish to add all the numbers between brackets and finally give its addition after applying the formula.

For ex. abc(200), def(300), gijklmno(5000), pqrstuvwx yz(50000) should give 55500 in another cell

Trying: abc(200), def(300), gijklmno(5000), pqrstuvwx yz(50000)

Expecting: 55500

CodePudding user response：

In case you don't have the newest functions, you could use something like:

=SUMPRODUCT(FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE(A1,")","("),"(","</b><b>")&"</b></a>","//b[number()=.]"))

which should work in 2013 or later, I think.

CodePudding user response：

You can try using a formula like shown below,

• Formula used in cell B1

=SUM(--BYCOL(TEXTSPLIT(A1,","),LAMBDA(x,TEXTBEFORE(TEXTAFTER(x,"("),")"))))

Alternative approach since you have not mentioned your Excel Version, the below formula works with Excel Windows 2010 & MAC

• Formula used in cell B1

=SUMPRODUCT(IFERROR(--TRIM(MID(
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"(",")"),")",","),",",REPT(" ",100)),
COLUMN($A$1:$Z$1)*100-99,99)),0))

Note: Depending on one's Excel version this needs to be keyed with CTRL SHIFT ENTER instead of Enter when exiting edit mode.

CodePudding user response：

Alternative:

=SUM(0 INDEX(TEXTSPLIT(A1,"(",")",,,0),,2))