Sum several values between brackets separated by line break in Excel-CodePudding

I would like to sum several values between brackets that are separated by line breaks in the same cell, where there are other characters.

I have tried the suggested formula

So in this case, the sum wanted is 1 2 2 3 1 3

Thank you,

Najoua

CodePudding user response：

Try:

Formula in A3:

=SUM(--INDEX(TEXTSPLIT(A1,{"(",")"},CHAR(10)),,2))

Or, for Excel 2013 (and higher):

=SUMPRODUCT(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"(",")"),CHAR(10),")"),")","</s><s>")&"</s></t>","//s[position() mod 3 = 2]"))