I have a hr and minute format given ie 1hr 27min in a cell. I need to convert it to either minutes or hours as a number only. Any help would be appreciated

It is formatted as text (1 hr 27min). There will never be days -- it will always be just hr min.

You have posted 2 different formats for the time. In your OP you used: 1hr 27min

In Response # 2 you used: 1 hr 27min

Note the extra space before

hrin Response # 2.Since we are going to have to parse the numbers out of the text string, it would help if we knew if the layout will be consistent, e.g. always a space after the numbers, never a space after the numbers, etc.

If the spaces will be there sometimes and other times not, then the difficulty level increases substantially.

If there will

alwaysbe a space after the numbers, then this should work:=MID(A3,1,FIND(" ",A3))+MID(A3,FIND(" ",A3,FIND(" ",A3)+1)+1,2)/60

If there will

neverbe a space after the numbers, then this should work:=MID(A3,1,FIND("h",A3)-1)+MID(A3,FIND(" ",A3)+1,FIND("m",A3)-FIND(" ",A3)-1)/60

Both formulas will return 1.45, depending on the presence of the spaces.

They should work for all values of hours (up to 13 digits) and both single and double digit minutes.

You can see how much difference the presence and location of the spaces make.

Excel was never really built to be a "text editor" so the the Text based functions can be quite cumbersome.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

There is never a space after the number. Your solution worked perfectly. Thanks!

Ask Your Question

Weekly Poll

Do you think Microsoft can save the Surface Book lineup?

Discuss in The Lounge

Poll History