Sum multiple numbers within a cell

Travis Myers

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.

Andrée Starå

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!

Travis Myers

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

Toufong Vang

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å

@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 

Travis Myers

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

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