Grabbing certain characters from a mixed character cell

[Canook05]

I am trying to create a report where I need to change the format of a cell from reading a time such as "5 hr(s) 30 min(s)" or "6 hr(s)" and only grab the numbers to convert them to a decimal - 5.5 and 6. I created some crazy formula by using three columns and separating part of the cell using the LEFT command and then breaking it down which worked well if the cells had hr and min in the cell, but when it only had hrs, it obviously did not work. I need to create something that would be able to grab the numbers from the cell. I was thinking maybe, if there was a command in excel, I could use an if statement such as "if cell contains "min," do this, else... The problem is I don't know how to make excel recognize "contains."

Could someone please help me??

In desparate need of help for work, otherwise I have to manually convert everything myself.

[Tony Valko]

Show us SEVERAL representative examples so we can see what we're dealing with.

[Canook05]

This is a quick file I just made up to show all of the possibilities that would pop up on my report that runs. I just need the decimal format.

[Tony Valko]

Try this...

=IF(COUNTIF(B4,"*hr*"),LEFT(B4,FIND(" ",B4)))+(IF(COUNTIF(B4,"*min*"),SUBSTITUTE(RIGHT(B4,9),"min(s)",""))/60)

[Canook05]

Oh my gosh THANK YOU SO MUCH!!! You just saved me about 5 hours of work!

[Tony Valko]

You're welcome. Thanks for the feedback!