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

SKR
SKR ✭✭
edited 10/09/23 in Formulas and Functions

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!

Answers

  • Paulo Ferrer
    Paulo Ferrer ✭✭✭✭

    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
    SKR ✭✭

    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å
    Andrée Starå ✭✭✭✭✭✭

    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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • SKR
    SKR ✭✭

    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
    Paul Newcome ✭✭✭✭✭✭

    How long is the max length of the string?

  • SKR
    SKR ✭✭

    Hi Paul!

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

  • Adrian Mandile CHESS
    Adrian Mandile CHESS ✭✭✭✭✭

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @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
    SKR ✭✭

    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
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!