How to sum the numbers in a text string?

[ronakbohra1994]

Hi

Could anyone help solve the below-attached problem?
Unable to figure the formula to get an answer for the stock mentioned in the attached excel sheet.

How can I do that by using the formula?

Thanks

[ronakbohra1994]

Could anyone help solve the below-attached problem?
Unable to figure the formula to get an answer for the stock mentioned in the attached excel sheet.

How can I do that by using the formula?

Thanks

[ronakbohra1994]

Could anyone help solve the below-attached problem?
Unable to figure the formula to get an answer for the stock mentioned in the attached excel sheet.

How can I do that by using the formula?

Thanks

[#4]

Hi @ronakbohra1994 

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

[#5]

@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.

[#6]

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-

@Riny_van_Eekelen 

[#7]

@ronakbohra1994 

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