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.. !!
Try:
Formula in B1:
=1440*TEXTAFTER(":0:"&SUBSTITUTE(SUBSTITUTE(A1,"m",""),"h",":"),":",-2)
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.
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)
Another legacy formula:-
=1440*SUBSTITUTE(SUBSTITUTE(IF(COUNTIF(A1,"*h*"),"","0h")&A1,"h",":"),"m","")
