Search code examples
excelformulafilterxml

How to use filterXML function to return numbers only?

Cell A1 have the following values:

ABC@10
Gg hh ii@20
BB@30

All numeric values must be after @.

I would like to add all numeric values, i.e. 10 + 20 + 30 = 60.

I asked chatGBT and it said it can use

=SUMPRODUCT(FILTERXML("<t><s>"&SUBSTITUTE(A1,"@","</s><s>")&"</s></t>","//s[number(.)=.]"))

However, the formula return the array of last number 30 only, not 10 and 20.

Is anyone know how to modify the formula, so the array formula can become {10,20,30}.

Any help or idea are appreciated. Thank you.

Solution

  • Change SUBSTITUTE(A1,"@","</s><s>") to SUBSTITUTE(SUBSTITUTE(A1,CHAR(10),"</s><s>"),"@","</s><s>").

    With Office 365:

    =SUM(--TAKE(TEXTSPLIT(A1,"@",CHAR(10)),,-1))

    Answers courtesy of Scott Craner.