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

 

[Rick Rothstein]

Just noting that if you change both of the A2 references in the formula you marked as the answer to A2:A11, it will generate the entire output from that one single formula (no copying down will be necessary)...

=TEXTBEFORE(A2:A11,"m")/(60*24)+IFERROR(TEXTAFTER(TEXTBEFORE(A2:A11,"s"),"m ")/(24*60*60),0)

Also, here is a shorter alternative solution that will also generate the entire output from a single formula (again, no copying down needed)...

=0+SUBSTITUTE(SUBSTITUTE("00:"&A2:A11&" "," m ",":")," s","")

 

[SimonHughes]

Glad we could help. Thanks for the feedback.

Follow up question which I should have anticipated. The data now includes the hours count as shown (hrs). I have tried to adjust the formulas you have all provided but with no success - can you please help? Many thanks
Problem you solved and new problem shown

23 m 17.747 s

1 hrs, 54 m 54.824 s

 

[SimonHughes]

Glad we could help. Thanks for the feedback.

More feedback Felix, the data now includes hrs (hours) which I should have anticipated. I have tried working this into the formulas provided but without success, can you help (again), many thanks

Original problem	32 m 26.875 s
New problem	1 hrs, 32 m 46.804 s

 

[SimonHughes]

Just noting that if you change both of the A2 references in the formula you marked as the answer to A2:A11, it will generate the entire output from that one single formula (no copying down will be necessary)...

=TEXTBEFORE(A2:A11,"m")/(60*24)+IFERROR(TEXTAFTER(TEXTBEFORE(A2:A11,"s"),"m ")/(24*60*60),0)

Also, here is a shorter alternative solution that will also generate the entire output from a single formula (again, no copying down needed)...

=0+SUBSTITUTE(SUBSTITUTE("00:"&A2:A11&" "," m ",":")," s","")

Hello Rick, the data now contains different content (which I should have anticipated) - could you be so kind as to tweak your solution for that?

Original problem	32 m 26.875 s
New problem	1 hrs, 32 m 46.804 s

 

[Dave Patton]

try the following

Time.xlsm
	A	B	C
1
2	23 m 17.747 s	0:23:17.747	0:23:17.747
3	1 hrs, 54 m 54.824 s	1:54:54.824	1:54:54.824
3dd

Cell Formulas
Range	Formula
B2:B3	B2	=--IF(LEN(A2)>13,TEXTJOIN(":",,TEXTSPLIT(A2,{"hrs,","m","s"})),TEXTJOIN(":",,TEXTSPLIT(A2,{"m","s"})))
C2:C3	C2	=--IF(ISERROR(FIND("h",A2)),TEXTJOIN(":",,TEXTSPLIT("0h"&A2,{"h","m","s"})),TEXTJOIN(":",,TEXTSPLIT(A2,{"hrs,","m","s"})))

 

[SimonHughes]

try the following

Time.xlsm
	A	B	C
1
2	23 m 17.747 s	0:23:17.747	0:23:17.747
3	1 hrs, 54 m 54.824 s	1:54:54.824	1:54:54.824
3dd

Cell Formulas
Range	Formula
B2:B3	B2	=--IF(LEN(A2)>13,TEXTJOIN(":",,TEXTSPLIT(A2,{"hrs,","m","s"})),TEXTJOIN(":",,TEXTSPLIT(A2,{"m","s"})))
C2:C3	C2	=--IF(ISERROR(FIND("h",A2)),TEXTJOIN(":",,TEXTSPLIT("0h"&A2,{"h","m","s"})),TEXTJOIN(":",,TEXTSPLIT(A2,{"hrs,","m","s"})))

Haha Dave, both are perfect - thank you so much. Between posting my question and getting your reply, I wondered if Power Query would fix it - so I am going to give that a bash now and will post afterwards.

 

[Dave Patton]

Try the following with your data.

Time.xlsm
	A			D
1
2	23 m 17.747 s			0:23:17.747
3	1 hrs, 54 m 54.824 s			1:54:54.824
4	5 m 17.747 s			0:5:17.747
5	1 hrs, 54 m 54.824 s			1:54:54.824
6	23 m 17.747 s			0:23:17.747
7
3dd

Cell Formulas
Range	Formula
D2:D6	D2	=--TEXTJOIN(":",,TEXTSPLIT(A2,{"hrs,","m","s"}))

Thanks for the feedback.

 

[SimonHughes]

Try the following with your data.

Time.xlsm
	A			D
1
2	23 m 17.747 s			0:23:17.747
3	1 hrs, 54 m 54.824 s			1:54:54.824
4	5 m 17.747 s			0:5:17.747
5	1 hrs, 54 m 54.824 s			1:54:54.824
6	23 m 17.747 s			0:23:17.747
7
3dd

Cell Formulas
Range	Formula
D2:D6	D2	=--TEXTJOIN(":",,TEXTSPLIT(A2,{"hrs,","m","s"}))

Thanks for the feedback.

That works too, many thanks!!!

 

[Bond00]

Is post #17 the final result answer or #15+#17 together? I dont see any difference in the result outputs.