Forum Discussion

wiscy's avatar
wiscy
Copper Contributor
Jan 12, 2021
Solved

There's a problem with this formula.

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 a...
excel
Formulas and Functions
  • JMB17's avatar
    JMB17
    Jan 12, 2021
    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's avatar
PeterBartholomew1
Silver Contributor
Jan 12, 2021

wiscy 

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.

  • wiscy's avatar
    wiscy
    Copper Contributor
    Jan 12, 2021

    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.

    • JMB17's avatar
      JMB17
      Bronze Contributor
      Jan 12, 2021
      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's avatar
        PeterBartholomew1
        Silver Contributor
        Jan 13, 2021

        JMB17 

        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.

         

Resources