Convert Text to Number?

David Bright

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

David Bright

Ooops, sent too soon.

 

I found the VALUE() Function! 

 

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

 

Thanks,

David

Travis

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)

Ted Cochran

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.

David Bright

Thanks everyone!

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

Thanks for the help.

Regards,

David