Forum Discussion

ronakbohra1994's avatar
ronakbohra1994
Copper Contributor
Jun 10, 2020

How to sum the numbers in a text string?

Hi If I have a text string in a cell below: "Apple 5 (+) Apple 10 (+) Apple 25 (+) Apple 20 (-)" And I would like to return the result of the sum of the numbers 5+10+25-20 "20" How can I do th...
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jun 10, 2020

ronakbohra1994 

As variant

=SUMPRODUCT(
    FILTERXML("<r>"&SUBSTITUTE(SUBSTITUTE(A1,"Apple ","<a>"),"(","</a>")&"</r>","//a")*
    FILTERXML("<r>"&SUBSTITUTE(SUBSTITUTE(A1,"(","<a>"),")","1</a>")&"</r>","//a")
)

Resources