Jan 12 2021 12:28 PM - edited Jan 12 2021 01:39 PM
I have a set of data that had a varying series of numbers separated by commas in a column of cells in Excel 2016. I used search and replace to change the commas to plus signs and then concatenated an equal sign to that data in a new cell in an attempt to create a formula that added together the numbers without having to retype everything. When I did that, even though the formula is simple (e.g., =12+987987+3452+7893) and appears correct, I get a message that says "There's a problem with this formula." It won't calculate even when I format the data type to number or general. How might I correct this error and get the formulas to resolve?
Jan 12 2021 02:00 PM
Although the result of the substitutions may look like a formula, it is in fact still a text string that happens to contain many digits. Options include
1. Using EVAL2 from Charles Williams's FastExcel library
2. Use a defined name containing the old Macro4 function EVALUATE
3. Write a UDF using VBA to access its EVALUATE function
4. Parse the string and convert each value into a number using the VALUE function, then sum them.
Following option 2, write a formula
= EVAL
in the cell immediately to the right of your formula. Go to Name Manager and create a new name EVAL which refers to
=EVALUATE(Sheet1!D5)
where D5 is a relative reference to your cell. The "=" can be inserted at the beginning of the string but the formulas will still work without it.
Jan 12 2021 02:26 PM
@Peter Bartholomew 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.
Jan 12 2021 03:33 PM
You can only use evaluate within a defined name; it does not work on the grid.
Jan 12 2021 03:46 PM
SolutionJan 12 2021 06:14 PM
@JMB17 This formula worked perfectly. Thanks!
Jan 12 2021 06:25 PM
Jan 13 2021 06:16 AM
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.
Jan 13 2021 07:54 AM
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.
Jan 13 2021 09:07 AM
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.
Jan 13 2021 09:52 AM
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.
Jan 13 2021 09:58 AM
I have nothing against FILTERXML in this concrete case, I'd only don't use it as TEXTSPLIT in general.
Jan 13 2021 10:11 AM
@Peter Bartholomew , will check, thank you. But I'd generalize using any text as delimiter, e.g. like "=@="