I'm trying to format a .csv for import somewhere, and I have a column of cells with plaintext formatted "Monday 02 23:11:52 " — I'd like to split them into two columns formatted with date (Monday, 02) and time (11:11PM)
2 Answers
Maybe you can also use the “Text to columns” functionality:
1.Select "Text to Columns"
2.Select "Space"
3.Enter formula in cell D1:
=A1& " " &B1
-
This helped me solve the problem and split the columns correctly, thank you. Gary's was also helpful, though the formulas were difficult for me to follow. Apr 25, 2020 at 17:00
With data in A1, in B1 enter:
=TRIM(MID(SUBSTITUTE(A1," ","|",2),1,FIND("|",SUBSTITUTE(A1," ","|",2))-1))
and in C1 enter:
=TIMEVALUE(TRIM(MID(SUBSTITUTE(A1," ","|",2),FIND("|",SUBSTITUTE(A1," ","|",2))+1,99)))
Then properly format C1: