Need Help on Converting Text to Time format

[HaramiPollins]

Hello Everyone,

I hope someone could help me out with providing a formula to convert text to time format. There is a report which I generate and it provide a raw dump in the below text format.

11m 29s
0s
0s
1h 4m 19s


I need to convert this text to time so that my Pivot can display the data. I would appreciate it if anyone can provide any suggestions. Thank you!

Regards,
Harami

[Special-K]

This works on the example data you gave in co0lumn A

in B1
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"m ",":"),"s",""),"h ",":")+0

[HaramiPollins]

Thank you Special-K.

I really appreciate your help on this .

Regards,
Harami

[Special-K]

Mm, This doesn't look like it will work if you have data like

28s (just seconds but not 0 as above)
2h (just hours)
4m (just minutes)
3h 2m (no seconds)
7m 12s (no hours)

Do you have data like this?