Friday, September 15, 2017

Convert ##m ##s formatted text to a number in seconds

Question:

Hello!

I'm hoping to find some way to convert this data into numbers so I can use those numbers for other formulas. I have checked the reporting system of the program I am pulling this info from and I have no choice but to pull this data in this format.

Here's the raw data:

NameResponse TimeDuration
Agent A1m 58s19m 27s
Agent B1m 3s18m 7s
Agent C49s16m 27s

and I would like to automatically convert it to this format:

NameResponse TimeDuration
Agent A118.001167
Agent B63.001087
Agent C49.00987
Can someone suggest a formula to do this? I have attempted to change the cells from "Automatic" to "Duration" or "Number", and nothing actually converts the numbers at all.

Solution:

If you have following data in Sheet1:


Then you can try the following formula in cell B2 of "Sheet2":

=ARRAYFORMULA(IFERROR(REGEXEXTRACT(Sheet1!B2:C4,"(\d+)m")*60)+IFERROR(REGEXEXTRACT(Sheet1!B2:C4,"(\d+)s")))