#### Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

# Convert Text to Number?

Options
✭✭✭✭
edited 12/09/19

Is there a way in a Smartsheet column to convert a Text value into a Numeric value?

For example, I have a column called "Duration", formatted as a "Text/Number" column type, and I have values in that column that are in the HH:MM:SS format, and look something like this: 00:15:00, 01:20:00, etc.

I'm trying to add a few of these together to calculate the "Total Duration", but there is not a simple way to do this in Smartsheet, so I am attempting to "dissassemble" each cell into parts, then add the parts together, and then "combine" the parts into the HH:MM:SS format.

So far I have been able to "dissassemble" the cell values into three new columns by using the LEFT(), MID(), and RIGHT() functions. This works, but when I do that the resulting cells contain text values not numerical values - which means that the SUM() function does not work.

Any ideas on how to convert the "deconstructed" cells into numerical values?

Thanks for the help!

Regards,

David

• ✭✭✭✭
Options

Ooops, sent too soon.

I found the VALUE() Function!

Now to figure out the "reconstruction" part....

Thanks,

David

• Employee
Options

Good to hear you figured it out! If your results are in multiple cells, you can reconstruct them by using the concatenate function. (=Cell1 + “:” + Cell2 + “:” + Cell3)

• Options

VALUE is your friend here.  A good trick is to use hidden columns in case you need intermediate steps.  Also, this helps if you want to take an iterative approach to your formulas (step by step using columns, then combining the steps into a long formula).  Hope this helps.

• ✭✭✭✭
Options

Thanks everyone!

I used all of your suggestiones and got it all working like a charm.

Thanks for the help.

Regards,

David

This discussion has been closed.