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.

• ✭✭✭✭✭✭
Options

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.

• Options

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

• ✭✭✭✭✭
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.

• ✭✭✭✭✭✭
Options

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.

• 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?

• ✭✭✭✭✭
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!