SUM of values separated by commas in the same cell ,

[Cassy_Amelia]

Hi,

Is there ANY solution to adding numbers separated by commas in the same cell?

I have used the SUBSTITUTE function to replace multi-select items with a numerical value and this has resulted in this kind of result:

3,2,1,4,1

Now I need to add those numbers so I get 11.

The closest I’ve found to a solution is in this post:

SUM of Values separated by a comma in the same sheet Ask the community

Try IF( {Your Variable}, VALUE( LEFT( {Your Variable}, FIND(',',{Your Variable})-1 ) )+ VALUE( RIGHT( {Your Variable}, LEN({Your Variable})-FIND(',',{Your Variable}) ) ) )

IF(
    {Your Variable},
    VALUE(
        LEFT(
            {Your Variable},
            FIND(',',{Your Variable})-1
            )
        )+
    VALUE(
        RIGHT(
            {Your Variable},
            LEN({Your Variable})-FIND(',',{Your Variable})
            )
        )
    )

However, it only works for 2 numbers separated by a comma, not multiple.

Any help is greatly appreciated!! Many thanks

[TheTimeSavingCo]

I don’t think so I’m afraid

You’ll need to extend the formula you mentioned in your post to handle the number of possible values that you’ve got I think

[kuovonne]

Cassy_Amelia:

Is there ANY solution to adding numbers separated by commas in the same cell?

What is the source of the cell value? If it is a lookup field, convert the field to a rollup field.

In most other cases, you could run a script.

If it is a text field that always has the same quantity of numbers, it is possible to use a formula field. Use combination of REGEX_EXTRACT() and VALUE().