how to calculate values in a single cell separated by a comma

I tried a few one-step ways of doing this through text parsing, regex, and more, and mostly came up blank.

I was able to get close using a hidden table, which may or may not work for your situation. It might also be a good starting point while I'm dig a little deeper..

In any case, starting with the table:

I created a second table called 'Calculation' with the same number of rows, and a number of columns that match the most values you would consider:

This table has the same number of header rows as the main table, and 0 header columns (this is important).

The formula for cell A2 is:

=VALUE(IFERROR(REGEX.EXTRACT(Table 1::$A2,"\d+",COLUMN(),0),0))

This somewhat complex formula does a couple of things.

First it performs a REGEX.EXTRACT to find the number in cell Table 1::$A2 that matches this column number (since we're in column 1, it finds the first number.

This result is checked for errors (i.e. to. make sure there's at valid result).

Then, since REGEX.EXTRACT returns a text value, we pass it through VALUE() to convert it to a number.

Now we have a way to extract the first number value from the string in Table 1::$A2

This formula can be filled across the row. Since we're using COLUMN() to determine which value to return, it automatically extracts the individual values into separate cells without us having to change anything.

Here's the reason why you need as many columns as you expect values - if you have 5 columns, but 6 values, the last one will be lost. Extra columns don't matter - they'll just be filled with 0 thanks to IFERROR().

The formula can then be filled down the rows to fill out the table. When done, you should get something like this:

SO basically what I've done is broken out the numbers in the comma-delimited cell into separate cells.

Now, this is easy to add up. Back in the main table, I can just set B2 to:

=SUM(Calculation::2:2)

This sums all the values in row 2 of the Calculation table.

This formula can be filled down to complete the table:

Some considerations:

1) The idea here is that you need a space to break out the values. I used a separate table for the calculations, but they could just as easily be (hidden?) columns in the main table. The only change is that B2 would be something like: SUM(C2:J2) where columns C through J contain the component numbers rather than a different table.

2) I didn't account for negative values - the regex "\d+" explicitly grabs numerals, not ± signs or other symbols. If you expect to support negative numbers, a different regex/parsing method could be used.

This is aggravating. It is so easy to extract it as a list/array of the individual numbers all in one cell except that they are string/text values and the numeric functions won't work on it. We need a set of numeric functions that will include text numbers. I was hoping AVERAGEA would since COUNTA does, but it does not. Well, it does if you do it directly like AVERAGEA("1", "3") but not if it uses cell references or, for this particular problem, AVERAGEA(REGEX.EXTRACT(A2,"\d+",0)).

I hear you. It sounds like we went down a similar rabbit hole looking for solutions.

I did hope that something like REGEX.EXTRACT which purports to return an array of values could be passed into SUM() (or any other numeric function), but it does look like the textual nature of the result is what's throwing it off.

No practical way via Numbers formula.

In Google sheets.

=SUM(SPLIT(A1, ","))

In Excel similar using VALUE and TEXTSPLIT.

SG