Sum multiple numbers within a cell

Options

Is it possible to "extract" the numbers from a cell like this?

I do it in Excel, but I coded a new function for it, so am unsure if Smartsheets has the ability to do it as well.

The formula would sum the numbers, in this case 3, in another column so I don't have to manually total it every time this situation appears.

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Hi @Travis Myers

    I hope you're well and safe!

    Yes, it's possible with a combination of RIGHT, LEFT, and more functions.

    Make sense?

    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 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:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

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

  • Travis Myers
    Options

    Using the example in the picture, how would such a formula look?

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    Options

    One approach might be...

    Find the position of the first "$".

    FIND("$", [Column_NN]@row)

    Starting from there, find the position of the first carriage return (this is located at the end of the first currency value).

    FIND( CHAR(10), [Column_NN]@row, FIND("$", [Column_NN]@row) )

     You now know where the first value starts, FIND("$", [Column_NN]@row), and ends, FIND( CHAR(10), [Column_NN]@row, FIND("$", [Column_NN]@row)).

    The length (number of characters) of the currency value is END_POSITION - START_POSITION or...

    (FIND(CHAR(10), [Column_NN]@row, FIND("$", [Column_NN]@row))) - (FIND("$", [Column_NN]@row)) 

     Use MID() to retrieve the value. The syntax is MID( string, start, num_of_characters)

    MID([Column_NN]@row, FIND("$", [Column_NN]@row)+1, ((FIND( CHAR(10), [Column_NN]@row, FIND("$", [Column_NN]@row) )) - (FIND("$", [Column_NN]@row)) ) )

    To find the position of the second "$", you can start the search FIND() at the first "$", or the first decimal ".".

    To find the position of the last "$", you would begin the search at the position of the second "$". Once you've found the position of the last "$", then you can use RIGHT() to retrieve the last value.


    Another approach...

    It would be too messy to keep this as one formula. For organization (and sanity) I would create 3 helper columns where the characters before each "$" would be replaced with "". Use REPLACE() to trim the string beginning with the first value, then iterate through the remaining.

    VAL_1=REPLACE([Column_NN]@row, 1, FIND("$", [Column_NN]@row), "")

    VAL_2=REPLACE([VAL_1]@row, 1, FIND("$", [VAL_1]@row), "")

    VAL_3=REPLACE([VAL_2]@row, 1, FIND("$", [VAL_2]@row), "")

    You can use LEFT() to retrieve the values from VAL_1, VAL_2, VAL_3.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    @Travis Myers

    I saw that Toufong answered already! My suggestion would be similar.

    Let me know if I can help with anything else!

    Best,

    Andrée 

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

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

  • Travis Myers
    Options

    If I do multiple columns like that add have the formulas in them, how do I make it stop at the first VAL column if there is only one entry?

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    Options

    You can check to see if FIND("$", [VAL_1]@row) = 0 and FIND("$", [VAL_2]@row) = 0 and return 0 or "" if so,

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!