Separating data in a cell

  • Thread starter Thread starter Adam
  • Start date Start date
A

Adam

I have data in cells that contains date and time
information as text. It is in the same format as below.

01 Sep 2003 10:16:53:000

How do I separate the date and time information? I have
tried to change the format for the cell, but since it is
basically a text string, no change occurs.

Thanks!
 
If it IS text, you can pull the time off of the date, or get both seperately
using something like
left(a1,11) for the date, and right(a1,12) for the time.
 
Select the column of cells
(ensure you have at least 2 empty cols to the right first)

Click Data > Text to Columns > Fixed width > Next

In step 2

double-click & remove the extra column breaks for the date
click to insert a column break just after the seconds "53"

the data preview should finally look like (with 2 col breaks):

01 Sep 2003 | 10:16:53 | :000

click Next

In step 3

for the 1st col (ie the date)
under Column data format
click "Date" > DMY

click on the 3rd col in data preview
Select "Do not import column (Skip)"

click Finish

This will give you the 2 cols - viz Date and the time that you want
 
With your data, say, in A1, in B1 enter the formila
=LEFT(A1,FIND(":",A1)-4), for the date part. In C1 enter the formula
=RIGHT(A1,LEN(A1)-FIND(":",A1)+3), for the time part.
Ilan
 
Back
Top