Forum Discussion

ronakbohra1994

Copper Contributor

Jun 10, 2020

How to sum the numbers in a text string?

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 that by using the formula?

Thanks

Formulas and Functions

2 Replies

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")
)

Sum Apples.xlsx18 KB

peteryac60
Iron Contributor
Jun 10, 2020
ronakbohra1994

Attached is a simple example - you extract the number (using MID function) and convert to number (using NUMBERVALUE function). Then you can sum them.

Of course if you have many different types of texts (e.g. TOMATO 20) you will need to adjust the formula e.g. use FIND/SEARCH functions to identify the space before the number , then use MID/NUMBERVALUE as above.

Hope this is of some help.

Peter

Apple.xlsx14 KB