Convert formatted hours to rounded decimal hours

Question

What I want is this:

These numbers are extracted from a script that I run on Selenium IDE. The problem is that those numbers come as text.

What I need is to convert, for example, "56h 29m" to "56.5" ... "27.75h" to "27.75".

Some of them are easy (like the first 2 columns), I just remove the last letter "h" and it's converted to number...the problem is when the numbers have this "h" in the middle with a space and the "m" at the end (last column).

Is there a way to implement this?

Answer 1

You need to break the hours and minutes apart so the minutes can be divided by 60. I am sure there is an easier way, but this works. It assumes your data is in Sheet1 starting in A2. Put the formula in another sheet (like Sheet2 A2) and copy down:

=IFERROR(if(right(Sheet1!A2,1)="h",regexreplace( Sheet1!A2, "[a-z]+" , "" ),concatenate(index(SPLIT(regexreplace(  Sheet1!A2, "[a-z]+" , "" ), " "), 0, 1),replace(round(index(SPLIT(regexreplace(  Sheet1!A2, "[a-z]+" , "" ), " "), 0, 2)/60,1),1,1,""))))