Convert Text to duration

[Mount Vernon Triangle]

Hello,

I've seen this questions posted in other variation in this forum, so I think you guys would know the trick.
I have downloaded a large file in excel with a column supposedly showing duration:

[TABLE="width: 81"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Example:
0h 5m 16s[/TD]
[/TR]
[TR]
[TD]0h 5m 33s[/TD]
[/TR]
[TR]
[TD]0h 6m 2s[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
It is however not letting add the number to get something like: Total= 0h16m51s or 16.9minutes. I would like to know for example the average time taken for each trip. I have over 10,000 entries, so counting by hand or editing the cells is out of the question here.

Thanks for all the help!

 

[Scott Huish]

Are there always h,m, and s in the original data even if the value for it is 0?

You could do a find and replace on the h(space) and replace with :
with m(space) and replace with :
replace s with nothing

Then take the average

 

[Mount Vernon Triangle]

Are there always h,m, and s in the original data even if the value for it is 0?

The data set is shown as is. There is always an h, m and s. Furthermore if I try to convert to other formats (time, general, accounting, numbers) it doesn't change anything. Any additions at this point result in 0.

 

[Mount Vernon Triangle]

Got it! Thanks Scott. I followed the find, replace instructions and managed to make it work