How to convert 58m32s to 00:58:32 or 72m10s to 01:12:10

I have a excel column with data like:

``````58m32s
72m10s
125m02s
``````

And would like to show it like:

``````00:58:32
01:12:10
02:05:02
``````

What formula combination should i use to make this possible? This should(?) work, but isn't:

=IF(MID(A1,3,1)="m",TIMEVALUE("00:"&LEFT(A1,2)&":"&RIGHT(A1,2)),TIMEVALUE(LEFT(A1,2)&":"&RIGHT(A1,2)&":00"))

Thanks

-

Well, you have different number of minutes in each case, so a single `MID` won't work. You might try:

``````=("00:"&SUBSTITUTE(SUBSTITUTE(A1,"m",":"),"s",""))*1
``````

The innermost `SUBSTITUTE` substitutes `m` by `:` (which is used in time) and the second removes `s`. You get the following after those two substitutes:

``````58:32
72:10
125:02
``````

If you just convert those to time now, Excel interpretes there as hh:mm, which is not your intent. Put a "00:" in front to fool Excel:

``````00:58:32
00:72:10
00:125:02
``````

Now it's in the `hh:mm:ss` format (somewhat), multiply by `1` to convert this into a value and format it as time: `hh:mm:ss`. This should do the trick :)

-
+1 for cheating Excel. But you have to convert the minutes to hours like the OP wants (thats not a big deal for you, but you forgot it^^) – nixda Aug 13 '13 at 18:37
@nixda Excel does it for you when you convert the value to the format `hh:mm:ss` =P. And thank you ^^ – Jerry Aug 13 '13 at 18:38
Ah missed the formatting thing. You're right – nixda Aug 13 '13 at 18:39
And it did! Thanks! – Khrys Aug 13 '13 at 18:43
@Khrys You're welcome :) – Jerry Aug 13 '13 at 19:31