data formatting - splitting a text/character string into 2 cells

[eddy_b]

I have a lot of data (circa 6,000 rows!) to clean.
In the date/time cells the entries appear as a text string like this:
2011-01-30T00:33:54.67
I need to separate it into two separate entries – one for the date and one for the time.
So for example
<TABLE style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; BORDER-COLLAPSE: collapse; BORDER-TOP: medium none; BORDER-RIGHT: medium none; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-yfti-tbllook: 1184; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt" class=MsoTableGrid border=1 cellSpacing=0 cellPadding=0><TBODY><TR style="mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black 1pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 231.05pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: black 1pt solid; BORDER-RIGHT: black 1pt solid; PADDING-TOP: 0cm; mso-border-alt: solid black .5pt; mso-border-themecolor: text1" vAlign=top width=308>Date
</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 231.05pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: black 1pt solid; BORDER-RIGHT: black 1pt solid; PADDING-TOP: 0cm; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1" vAlign=top width=308>Time
</TD></TR><TR style="mso-yfti-irow: 1; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black 1pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 231.05pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 1pt solid; PADDING-TOP: 0cm; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1" vAlign=top width=308>2011-01-30
</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 231.05pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 1pt solid; PADDING-TOP: 0cm; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; mso-border-bottom-themecolor: text1; mso-border-right-themecolor: text1" vAlign=top width=308>00:33:54
</TD></TR></TBODY></TABLE><?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
I’m keen to do this to allow me to sort the data by date or time. At present, the formatting means Excel can’t make sense of it.
Is there a quick way of separating it out?
Obviously I can go through each one manually and select, cut and paste the info into separate cells... but this would take forever!
Any help much appreciated!
Thanks in advance
Eddy

 

[Darren Bartrup]

With your text string in cell A1, this formula should do the trick:

Date:

=DATE(YEAR(LEFT(A1,FIND("T",A1)-1)),MONTH(LEFT(A1,FIND("T",A1)-1)),DAY(LEFT(A1,FIND("T",A1)-1)))

Time:

=TIMEVALUE(LEFT(MID(A1,FIND("T",A1)+1,LEN(A1)),LEN(MID(A1,FIND("T",A1)+1,LEN(A1)))-3))

The time formula ignores the final three figures of your text string - '.67', so hope it will always be 3 figures or will it sometimes be '.1'?

 

[eddy_b]

Thanks ever so much Dan! I'll give it a whirl now.

Eddy

 

[eddy_b]

Apologies Darren, mis-read your name in my enthusiasm.

 

[Jonmo1]

You might also try

Highlight the column of data
Click Data - Text to Columns
Select Deliminated - click next
Check "Other" and put a T
Click Finish

Hope that helps.

 

[Darren Bartrup]

Good point Jon.

I'm always forgetting about the built in functionality..... which I'm always pointing out to my work colleague when he writes VBA routines for problems that just need a simple formula.

 

[eddy_b]

That is awesome. Thanks ever so much guys!

Eddy