How do I add a series on numbers that is stored as a string in a cell? e.g. "20+10+10+40"

SKR · 10/09/23

I have a cell that has a string of "20+10+10+40". I want to add all these numbers together. They are stored in a cell as a string. The length of string is not fixed. Sometimes it could be like "5+6+30+50+2+2+3". Hoping to find some answers. Thanks!

Paulo Ferrer · 10/09/23

I've already done something similar using JOIN + COLLECT (number selection conditions).

It will work.

Like this: =JOIN(COLLECT(Numbers:Numbers, Numbers:Numbers, >0))

SKR · 10/10/23

Hi Paulo Ferre,

Thanks for the suggestion. My string "20+10+10+40" is in a cell. I need to pick out the numbers and add up the numbers only.

Your formula seems to be collecting values from a few cells and joining them together. Example Cell 1 has "1", Cell 2 has "2", and Cell 3 has "3". Applying the formula you suggested gives 123.

Hope that clarifies my question.

Andrée Starå · 10/10/23

Hi @SKR

I hope you're well and safe!

Can you share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

SKR · 10/11/23

Hi Andrée Starå,

This is screen shot.

This is something that I have worked out yesterday. But i think there must be a more effective way of doing it..

#Copied the cell out to another sheet, get the length of the string "02+05+04"

# then in a another cell, get the last two char "04" using RIGHT function and change to value

#Just below the original string, I used LEFT function, count then length of string and remove the last three characters "+04"

# that Gets repeated until all the numbers in the string is completely removed.

I have worked on the assumption that they are all two digit numbers, and yes probably in my case it will be so.

If the would be a better way to approach this please do share. Appreciate it very much..

Thanks!🙂

Paul Newcome · 10/11/23

How long is the max length of the string?

SKR · 10/12/23

Hi Paul!

The length varies. The max I have seen is 26 sets of double digits. ( 01+02+....+25+26)

Adrian Mandile CHESS · 10/12/23

Could you put the original string inside a SUBSTITUTE() function that replaces “+” with “,” and then SUM the resulting string?

Paul Newcome · 10/12/23

@Adrian Mandile CHESS That won't work. The SUBSTITUTE function outputs a text string and not necessarily a series of values.

The only option we have is to parse out each number individually and then add them together.

The below will grab the first number and convert it into a numerical value:

=IFERROR(VALUE(MID("!" + [Column Name]@row + "!", FIND("!", SUBSTITUTE("!" + [Column Name]@row + "!", "+", "!", 1) + 1, FIND("!", SUBSTITUTE("!" + [Column Name]@row + "!", "+", "!", 2) - (FIND("!", SUBSTITUTE("!" + [Column Name]@row + "!", "+", "!", 1) + 1))), 0)

Each bold number above would be adjusted to pull the next number. So to pull the second number you would use 2/3/2, the third number would be pulled with 3/4/3, so on and so forth.

2nd number:

=IFERROR(VALUE(MID("!" + [Column Name]@row + "!", FIND("!", SUBSTITUTE("!" + [Column Name]@row + "!", "+", "!", 2) + 1, FIND("!", SUBSTITUTE("!" + [Column Name]@row + "!", "+", "!", 3) - (FIND("!", SUBSTITUTE("!" + [Column Name]@row + "!", "+", "!", 2) + 1))), 0)

3rd number:

=IFERROR(VALUE(MID("!" + [Column Name]@row + "!", FIND("!", SUBSTITUTE("!" + [Column Name]@row + "!", "+", "!", 3) + 1, FIND("!", SUBSTITUTE("!" + [Column Name]@row + "!", "+", "!", 4) - (FIND("!", SUBSTITUTE("!" + [Column Name]@row + "!", "+", "!", 3) + 1))), 0)

We have a couple of options on how to do this next step, but regardless we are going to need to use some helper columns since there is the possibility of so many. You could use a helper column for each number (26 helper columns with shorter formulas), or you can group sets of number formulas together to cut down on helper columns but have longer formulas.

=IFERROR(1st number formula, 0) + IFERROR(2nd number formula, 0) + IFERROR(3rd number formula, 0) + ...............

Once you decide whether you want more helper columns with shorter formulas or longer formulas in less helper columns, you would then need to add these helper columns together.

SKR · 10/25/23

https://community.smartsheet.com/discussion/comment/399643#Comment_399643

Nope , that didn't wokt

SKR · 10/25/23

Hi Paul,

Thanks for the suggestion. I think that is also something similar to what I had worked in the above reply earlier. Each number has to be parsed out..

It comes to either having a very long formula in on line or use helper columns..

Appreciate your time and effort in coming up with a resolution..🙇‍♂️

Paul Newcome · 10/25/23

Happy to help. 👍️

Formulas and Functions

Formulas and Functions

How do I add a series on numbers that is stored as a string in a cell? e.g. "20+10+10+40"

Answers

Help Article Resources

Quick Links

Categories

Trending in Formulas and Functions

Concatenate formula to combine Date and Time into unique code

How to fix adding 1 or -1 to date difference calculations

Calculating KM Distance between Lat and Long