Forum Discussion
There's a problem with this formula.
- One other way I believe you can do it. Assuming your comma delimited text string of numbers is in cell A1:
=SUMPRODUCT(FILTERXML("<L><I>"&SUBSTITUTE(A1,",","</I><I>")&"</I></L>","//I"))
I experimented with the XML solution using modern Excel 365 functions. First LET, which allows local names to be introduced in order to remove nested formulas.
= LET(
XML, "<list><item>" &
SUBSTITUTE(listCosts,",","</item><item>") &
"</item></list>",
list, FILTERXML(XML,"//item"),
SUM(list) )XML is the markup language version of the CSV string, list is it as separate items and, since all calculation is performed as array calculation in 365, SUM will do.
The next step is to wrap the LET with the newly developed LAMBDA function which allows a user to pass different parameters to the same function.
= LAMBDA(listCosts,
LET( ...
... )
)(ItemCosts)Naming the function TotalXML, gives a final form for the function call of
= TotalXML(ItemCosts)I did also write a recursive Lambda function TotalCSV, using only basic text handling functions, but this is not the place to describe the solution.
I don't have office 365 on my home machine. And, even though it's on my work machine, not only are we in the slowest channel, but the IT department packages and deploys the updates themselves, so we're actually months behind the slowest channel.
Yes, I think a textsplit function, if they've released one, would be better. The issue of filterxml errors due to special characters doesn't appear to apply based on the OP's description of the data.
I have nothing against FILTERXML in this concrete case, I'd only don't use it as TEXTSPLIT in general.
