Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Oct 27, 2022 08:04 AM
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
Nov 02, 2022 04:19 AM
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
Nov 02, 2022 04:39 AM
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().