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"))
PeterBartholomew1 I can't complete creation of the formula in the attached image because I get the error message. In addition, I don't see Evaluate as a function within Excel 2016. I get a #NAME! error creating the formula, as indicated.
=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.
I'd do not rely on FILTERXML with functions like TEXTSPLIT. Try to split text like "a, b&c, d". Ampersand is not the only symbol with which it doesn't work, you may google for all of them.
With LET/LAMBDA you may build such function without FILTERXML, and it will be more reliable.
Hi Sergei
I had used the problem to challenge my thinking using the newly available LAMBDA functions.
I did also create a recursive formula, based upon a rework of the Fibonacci recursion spreadsheet. There must be times when I irritate members with my alien solutions and I felt that a recursive solution was a couple of steps too far here. However, to prove it exists
= TotalCSV(ItemCosts) where TotalCSV refers to = LAMBDA(residual, LET( n, IFERROR( FIND(",", residual), 1+LEN(residual)), item, VALUE(LEFT(residual, n-1)), reduced, REPLACE(residual, 1, n+1,""), partialSum, IF(reduced<>"", TotalCSV(reduced), 0), partialSum + item ) )(ItemCosts)I offer apologies for those who will not be in a position to evaluate such methods, and may not be for many years. We seem to have reached a point where Excel 365, though it can run legacy spreadsheet solutions, is actually an utterly different platform which supports a programming approach to solving problems. When and whether the two strategies will ever come together once more, I am not so sure.
- Quite welcome. Glad to hear it worked for you.
