Excel Change "x h x m" Format into minutes only-CodePudding

I am Trying to change the Excel cells which contain hours and minutes with "h" and "m" into minutes only. The Problem here that the letters h & m are in the cell.

What i need it to look like:

2h 30m --> 150

4h --> 240

51m --> 51

Please help me out. thanks in advance.. !!

CodePudding user response：

You could try something like this:

=IF(ISNUMBER(SEARCH("h";A1));VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1;" ";"");"h";":");"m";""))*24*60;VALUE(SUBSTITUTE(A1;"m";"")))

Notice there is an IF to separate cases when there is only minutes from cases when there is also hours (calculus is different)

CodePudding user response：

So find the "h" to get the hours like so:

=left(A2,find("h",A2,1)-1)

Then multiply the result by 60 (as 60 minutes in an hour)

For minutes:

=mid(A2,find("h",a2,1) 1,2)

This assumes that minutes is double digit as per your example, you could make it more precise by finding the position of the "m" and calculating the difference.

Then sum the result to get minutes.

CodePudding user response：

Try:

Formula in B1:

=1440*TEXTAFTER(":0:"&SUBSTITUTE(SUBSTITUTE(A1,"m",""),"h",":"),":",-2)