1

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)

Screenshot

Improve this question

2 Answers 2

Reset to default
0

Maybe you can also use the “Text to columns” functionality:

1.Select "Text to Columns"

enter image description here

2.Select "Space"

enter image description here

3.Enter formula in cell D1:

=A1& " " &B1

enter image description here

4.The same way to format dates as Gary does: enter image description here

Improve this answer
1
  • 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.
    – Visby Green
    Apr 25, 2020 at 17:00
0

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:

enter image description here

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .