Best way to Convert time stored as text, at different lengths to a standard time unit?

[AYouQueTai]

Hello,

Wondering how you all would go about this. I have been mucking around with this all afternoon trying to go from LEFT to RIGHT and using LEN to come up with a formula that works for all of the following entries.
How would you standardize this? There are no seconds when there are hours. the minutes go from left to right depending on if there are hours. If there are 1-9 units it is a single digit rather than always having 2, so it moves the hour, minute or second identifier. Is there an easy way to do this?

Book2
	A	B
1	Idling Time	(The Goal) Idle Time
2	5m 8s	00:05:08
3	5m 11s	00:05:11
4	14m 33s	00:14:33
5	1h 2m	01:02:00
6	10h 5m	10:05:00
7	12h 15m	12:15:00
Sheet1

Thanks,

 

[Cubist]

Try:

Book1
	A	B	C
1	Idling Time	(The Goal) Idle Time
2	5m 8s	0:05:08	<- formatted [h]:mm:ss
3	5m 11s	0:05:11
4	14m 33s	0:14:33
5	1h 2m	1:02:00
6	10h 5m	10:05:00
7	12h 15m	12:15:00
8	12h 15m 30s	12:15:30
Sheet4

Cell Formulas
Range	Formula
B2:B8	B2	=LET( r,REPLACE(A2,1,0," "), h,TEXTAFTER(TEXTBEFORE(r,"h",,,,0)," ",-1,,,0), m,TEXTAFTER(TEXTBEFORE(r,"m",,,,0)," ",-1,,,0), s,TEXTAFTER(TEXTBEFORE(r,"s",,,,0)," ",-1,,,0), TIME(h,m,s))

 

[Dave Patton]

Dates and Time 2024.xlsm
	A	B	C
1	Idling Time	(The Goal) Idle Time
2	5m 8s	0:05:08	0:05:08
3	5m 11s	0:05:11	0:05:11
4	14m 33s	0:14:33	0:14:33
5	1h 2m	1:02:00	1:02:00
6	10h 5m	10:05:00	10:05:00
7	12h 15m	12:15:00	12:15:00
8
3h

Cell Formulas
Range	Formula
C2:C7	C2	=IF(IFERROR((FIND("h",A2)>0),0),LET(t,TEXTSPLIT(A2,{"h ","m"}),TIME(INDEX(t,1),INDEX(t,2),0)),LET(t,TEXTSPLIT(A2,{"m ","s"}),TIME(0,INDEX(t,1),INDEX(t,2))))

 

[rlv01]

One more.

Book1
	A	B
1	Idling Time	(The Goal) Idle Time
2	5m 8s	00:05:08
3	5m 11s	00:05:11
4	14m 33s	00:14:33
5	1h 2m	01:02:00
6	10h 5m	10:05:00
7	12h 15m	12:15:00
Sheet1

Cell Formulas
Range	Formula
B2:B7	B2	=TIMEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(IFERROR(FIND("h",A2),0)= 0,"0h" & A2,A2),"h",":"),"m",":"),"s","")," ",""))

 

[StephenCrump]

Another:

=LET(t,TEXTSPLIT(A2," ",,1),SUM(LEFT(t,LEN(t)-1)/LOOKUP(RIGHT(t,1),{"h","m","s"},{24,1440,86400})))

 

[shaowu459]

工作簿1
	A	B	C	D	E
1	Idling Time	(The Goal) Idle Time
2	5m 8s	00:05:08		00:05:08
3	5m 11s	00:05:11		00:05:11
4	14m 33s	00:14:33		00:14:33
5	1h 2m	01:02:00		01:02:00
6	10h 5m	10:05:00		10:05:00
7	12h 15m	12:15:00		12:15:00
8
Sheet1

Cell Formulas
Range	Formula
D2:D7	D2	=LET(s,{"h","m","s"},t,TEXTSPLIT(A2," "),--TEXTJOIN(":",,XLOOKUP("*"&s,t,TEXTSPLIT(t,s),0,2)))

 

[smozgur]

Some more (trying to use the new beta Regex functions):

Book1
	A	B	C	D
1	Idling Time	(The Goal) Idle Time	Regex (Beta)	Let/Texsplit/Map/Lambda
2	5m 8s	00:05:08	00:05:08	00:05:08
3	5m 11s	00:05:11	00:05:11	00:05:11
4	14m 33s	00:14:33	00:14:33	00:14:33
5	1h 2m	01:02:00	01:02:00	01:02:00
6	10h 5m	10:05:00	10:05:00	10:05:00
7	12h 15m	12:15:00	12:15:00	12:15:00
Sheet1

Cell Formulas
Range	Formula
C2:C7	C2	=LET(pattern, "((\d+)h)?\s?((\d+)m)?\s?((\d+)s)?\s?", hour, NUMBERVALUE(REGEXREPLACE(A2,pattern,"$2")), min, NUMBERVALUE(REGEXREPLACE(A2,pattern,"$4")), sec, NUMBERVALUE(REGEXREPLACE(A2,pattern,"$6")), TEXT(TIME(hour,min,sec), "hh:mm:ss") )
D2:D7	D2	=TEXT( SUM( LET(split,TEXTSPLIT(A2," "), MAP(split,LAMBDA(part,LET(s,RIGHT(part,1),n,LEFT(part,LEN(part)-1),IF(s="h",3600*n,IF(s="m",60*n,IF(s="s",1*n)))))) ) )/60/60/24, "hh:mm:ss" )

 

[shaowu459]

if Regex functions are available:

aaa.xlsx
	A	B	C	D	E
1	5m 8s	00:05:08	00:05:08	TRUE
2	5m 11s	00:05:11	00:05:11	TRUE
3	14m 33s	00:14:33	00:14:33	TRUE
4	1h 2m	01:02:00	01:02:00	TRUE
5	10h 5m	10:05:00	10:05:00	TRUE
6	12h 15m	12:15:00	12:15:00	TRUE
7
Sheet1

Cell Formulas
Range	Formula
C1:C6	C1	=--TEXTJOIN(":",,IFNA(REGEXEXTRACT(A1,"\d+(?="&{"h","m","s"}&")"),))
D1:D6	D1	=B1=C1

 

[smozgur]

=--TEXTJOIN(":",,IFNA(REGEXEXTRACT(A1,"\d+(?="&{"h","m","s"}&")"),))

This is beautiful!
Regex functions that work with arrays are also beautiful!

 

[Dave Patton]

=--TEXTJOIN(":",,TEXTSPLIT(A2&" 0s",{"h ","m ","s"}))