Skip to main content

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:



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

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


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().

Reply


Terms & Conditions