Forum Discussion
How to sum the numbers in a text string?
4 Replies
If formula I'd suggest same approach as previous time
=SUMPRODUCT( FILTERXML("<r>"&SUBSTITUTE( SUBSTITUTE(LOWER(TEXTJOIN(" ",1,IF(LEFT($A$3:$D$17,LEN($F3))=$F3,$A$3:$D$17,""))),LOWER(F3),"<a>"), "(","</a>")&"</r>","//a")* FILTERXML("<r>"&SUBSTITUTE( SUBSTITUTE(LOWER(TEXTJOIN(" ",1,IF(LEFT($A$3:$D$17,LEN($F3))=$F3,$A$3:$D$17,""))),"(","<a>"), ")","1</a>")&"</r>","//a") )ronakbohra1994 You asked a similar question not too long ago, but than only with "Apple" and you probably didn't get the answer you wanted since you need to include more that just "Apple". I wonder why you work with these text strings containing of a name a number and a plus or minus sign in brackets. So much easier to work with numbers. If you are familiar with PowerQuery, this is relatively easy to perform as shown in the attached file.
Well, the solution attached doesn't solve my problem since the data keeps on changing for every round in the same format.
However, if the format needs to be changed that can be done.i.e. from Apple 10 (-) it can be changed to Apple -10 or Apple 10-
I'm not sure about what exactly you want to SUM.
From what I see, you give labels and those labels are converted into points of some sort, points that you want to SUM.
I think this can be done with a conversion table and a VLOOKUP to convert those labels into points.
Kind regards
Hans
