Forum Discussion

ronakbohra1994's avatar
ronakbohra1994
Copper Contributor
Jun 11, 2020

How to sum the numbers in a text string?

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 formu...
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jun 11, 2020

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

 

Resources