Convert text to time

[SimonHughes]

Hello All, is there any way to convert these times into a proper time format? TIA

GC TIME
28 m 34.846 s
31 m 1.347 s
31 m 11.698 s
31 m 38.859 s
32 m 8.944 s
32 m 12.423 s
32 m 26.875 s
32 m 53.788 s
32 m 56.865 s
33 m

 

[DanteAmor]

Try:

=TIMEVALUE("00:" & TRIM(TEXTBEFORE(A2,"m",,1,,"00:")) & TRIM(SUBSTITUTE(TEXTAFTER(A2,"m",,1,,"00"),"s","")))

Use this format cell:
hh:mm:ss.000

 

[Dave Patton]

Time.xlsm
	A	B
1	GC TIME
2	28 m 34.846 s	00:28:34.846
3	31 m 1.347 s	00:31:01.347
4	31 m 11.698 s	00:31:11.698
5	31 m 38.859 s	00:31:38.859
6	32 m 8.944 s	00:32:08.944
7	32 m 12.423 s	00:32:12.423
8	32 m 26.875 s	00:32:26.875
9	32 m 53.788 s	00:32:53.788
10	32 m 56.865 s	00:32:56.865
11	33 m	00:33:00.000
12
3d

Cell Formulas
Range	Formula
B2:B11	B2	=TEXTBEFORE(A2,"m")/(60*24)+IFERROR(TEXTAFTER(TEXTBEFORE(A2,"s"),"m ")/(24*60*60),0)

 

[SimonHughes]

Try:

=TIMEVALUE("00:" & TRIM(TEXTBEFORE(A2,"m",,1,,"00:")) & TRIM(SUBSTITUTE(TEXTAFTER(A2,"m",,1,,"00"),"s","")))

Use this format cell:
hh:mm:ss.000

Thanks Dante but it is returning a surprise

GC TIME	Converted
28 m 34.846 s	00:47:14.846
31 m 1.347 s	00:05:11.347
31 m 11.698 s	00:51:51.698
31 m 38.859 s	00:52:18.859
32 m 8.944 s	00:05:28.944
32 m 12.423 s	00:53:32.423

 

[DanteAmor]

Try:

=TIMEVALUE("00:" & TRIM(TEXTBEFORE(A2,"m",,1,,"00:")) & ":" & TRIM(SUBSTITUTE(TEXTAFTER(A2,"m",,1,,"00"),"s","")))

 

[felixstraube]

Another option:

Book1.xlsx
	A	B
1	GC TIME
2	28 m 34.846 s	00:28:34.846
3	31 m 1.347 s	00:31:01.347
4	31 m 11.698 s	00:31:11.698
5	31 m 38.859 s	00:31:38.859
6	32 m 8.944 s	00:32:08.944
7	32 m 12.423 s	00:32:12.423
8	32 m 26.875 s	00:32:26.875
9	32 m 53.788 s	00:32:53.788
10	32 m 56.865 s	00:32:56.865
11	33 m	00:33:00.000
Sheet6

Cell Formulas
Range	Formula
B2:B11	B2	=SUM(IFERROR(TEXTSPLIT(A2,{"m","s"},,0)/{1440,86400}, 0))

 

[SimonHughes]

Another option:

Book1.xlsx
	A	B
1	GC TIME
2	28 m 34.846 s	00:28:34.846
3	31 m 1.347 s	00:31:01.347
4	31 m 11.698 s	00:31:11.698
5	31 m 38.859 s	00:31:38.859
6	32 m 8.944 s	00:32:08.944
7	32 m 12.423 s	00:32:12.423
8	32 m 26.875 s	00:32:26.875
9	32 m 53.788 s	00:32:53.788
10	32 m 56.865 s	00:32:56.865
11	33 m	00:33:00.000
Sheet6

Cell Formulas
Range	Formula
B2:B11	B2	=SUM(IFERROR(TEXTSPLIT(A2,{"m","s"},,0)/{1440,86400}, 0))

Thanks you Felix, works perfectly!

 

[SimonHughes]

Try:

=TIMEVALUE("00:" & TRIM(TEXTBEFORE(A2,"m",,1,,"00:")) & ":" & TRIM(SUBSTITUTE(TEXTAFTER(A2,"m",,1,,"00"),"s","")))

Hi Dante, yes, that works, many thanks

 

[SimonHughes]

Time.xlsm
	A	B
1	GC TIME
2	28 m 34.846 s	00:28:34.846
3	31 m 1.347 s	00:31:01.347
4	31 m 11.698 s	00:31:11.698
5	31 m 38.859 s	00:31:38.859
6	32 m 8.944 s	00:32:08.944
7	32 m 12.423 s	00:32:12.423
8	32 m 26.875 s	00:32:26.875
9	32 m 53.788 s	00:32:53.788
10	32 m 56.865 s	00:32:56.865
11	33 m	00:33:00.000
12
3d

Cell Formulas
Range	Formula
B2:B11	B2	=TEXTBEFORE(A2,"m")/(60*24)+IFERROR(TEXTAFTER(TEXTBEFORE(A2,"s"),"m ")/(24*60*60),0)

Hi Dave, perfect, many thanks indeed

 

[felixstraube]

Glad we could help. Thanks for the feedback.