Converting "x hours y minutes" into "z minutes" or "hr:mn"

[MattChristensen]

I have a data set that lists durations in a column as "x hours y minutes". I need to average all the durations. Does anyone know of a formula I can use to take out the words "hours" and "minutes" so that the cells either read "z minutes" or "hr:mn"?

Thanks so much!

[Lemice]

Hello,

Assuming the "x hours y minutes" is in A1, paste this formula in B1

Please Login or Register  to view this content.

This will calculate the minutes

Paste this formula in C1

Please Login or Register  to view this content.

To convert it into hh:mm (they are all numbers not texts, you can do whatever with them)

And here is a sample file.
Note that if you put a number with more than 2 digits, the formula will return wrong value (and who put 3 digits number as hours or minutes anyway). Another note is that you don't have to care if minute and hour are plural or not (2 hour and 20 hours, still return the correct amount, don't worry).

[daddylonglegs]

If you always consistently have "hours" and "minutes" as shown then you can use this formula to convert

=SUBSTITUTE(SUBSTITUTE(A1," hours ",":")," minutes","")+0

[MattChristensen]

Thank you guys so much! Massively helpful!