Convert number and text cell time to decimal cell time for Google Sheets

[toplad]

I've got an issue with some time data that is exported from a program like this into cells (each number with text in each cell descending all in column a)

0h

14h

17h

17h

25h 30m

0h

8h 30m

7h

I need to somehow have this converted in to decimal time 25.5 the main problem is that it has text it the cells (h and m) which I cant remove to even start converting it. Any ideas the best way to solve this?

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B
1
2	0h	0
3	14h	14
4	17h 15m	17.25
5	17h	17
6	25h 30m	25.5
7	0h	0
8	8h 30m	8.5
9	7h	7
Main

Cell Formulas
Range	Formula
B2:B9	B2	=IF(ISNUMBER(FIND("m",A2)),SUBSTITUTE(SUBSTITUTE(A2,"h ",":"),"m",":0"),SUBSTITUTE(A2, "h",":0"))*24

 

[toplad]

This is amazing, youd def laugh if i showed you what i was trying...it involved about 8 columns trying to split it out! works in excel but now i've moved it into google sheets it throws me this 'Function MULTIPLY parameter 1 expects number values. But '0:0' is a text and cannot be coerced to a number.' Any way round this?

 

[Fluff]

In future if you need something for a spreadsheet other than Excel, you should make that clear in the title, or post & it should be posted in the General Discussion & Other Applications section os the site.
I have done that for you this time.

 

[toplad]

Sorry i should have said that its used in excel and in sheets (thought they worked the same)

 

[Fluff]

I've no idea how sheets handles time, but try

=IF(ISNUMBER(FIND("m",A2)),SUBSTITUTE(SUBSTITUTE(A2,"h ",":"),"m",":0"),SUBSTITUTE(A2, "h",":0:0"))*24

 

[toplad]

Thank you for doing that!

The only odd one which comes up in both formulas is that 4h 45m formats to 4.75

 

[Fluff]

That's what it should be. 45m is 3/4 of an hour.

 

[toplad]

Sorry you're right, its been a long day staring at this sheet trying to solve what you've done.

Thank you and if you're every near Scarborough I owe you a pint or any other drink of your choice!

 

[Fluff]

Glad to help & thanks for the feedback.