Formatting a value

[AlexanderBB]

Cell type is General and holds a string like 02 33 or 1 02 33
I've used replace to fix "03:22" and done a check for a leading zero.
But I think there may be a more elegant method, perhaps Format or RegEx.
The value can be typed in by hand or data from a Recordset with VBA.
Could it also be trapped if the value can't be made?

 

[Skyybot]

But it's always a Time? And uses Space as a delimiter?

 

[Skyybot]

Like this?

Book1
	A	B
1	02 33	2:33:00 AM
2	01 02 33	1:02:33 AM
3	23 59	11:59:00 PM
4	1 02 33	1:02:33 AM
PP_Total

Cell Formulas
Range	Formula
B1:B4	B1	=ChangeToTime(A1)

 

[Skyybot]

Book1
	A	B
1	02 33	2:33:00 AM
2	01 02 33	1:02:33 AM
3	23 59	11:59:00 PM
4	1 02 33	1:02:33 AM
5	24 00
6	1 5	1:05:00 AM
7	00 00 01	12:00:01 AM
8	6 30	6:30:00 AM
9	25 63
PP_Total

Cell Formulas
Range	Formula
B1:B9	B1	=IFERROR(ChangeToTime(A1),"")