Is there a way to add a formula to unique data points in excel?-CodePudding

I am trying to clean some data with this format:

A1 B1 C1 "0,1,0,E,1" "0,0,0,E" "0,1,1,1,2,E"

To obtain the information, I will need to sum up each cell individually, and then calculate the sum over a row.

So far, I have replaced all of the "E"s (for empty) with no data, which removed the E's, and I have replaced all of the commas with " " signs to add the numbers in the cell. To run the formula of each cell, I will need to now place an "=" in front of each text string, however, if I copy it in with something like "="="&A1" the formula will not run because excel is reading the = as a letter or symbol and not an operator. Do you know of a way to fix this problem?

Thank you so much!

CodePudding user response：

I think this will work for your version ...

=SUM(FILTERXML("<d><c>" & SUBSTITUTE(A1,",","</c><c>") & "</c></d>","//c"))

That's applied like below ...

... there's always someone smarter than me but that seems to work.

It’s not an in place replacement but it keeps your source data intact and provides a nice reconciliation point.

CodePudding user response：

Using a separate sheet for each column of values, given data in cell A1 of

0,0,0,1,0,1,1,E,1,0

In C1, enter the formula

=IFERROR(MID(SUBSTITUTE(SUBSTITUTE($E$14,",",""),"E",""),COLUMN()-2,1)*1, "")

and drag the fill handle to to the right for as many cells as the longest number of data points you have.

Enter your SUM()formula in B1 (you can use the shortcut ALT =).