Split date ##/##/#### into 3 columns

[AndiH]

I would like to split a column of dates, eg 1/5/2012 into 3 columns at the "/". I've added the 3 new columns and tried "Text to Columns" but it asks if I want to overwrite the existing but I can't see the option to do otherwise. If I do that anyway, when the cells are split I get 3 different bizarre dates in the same format: Col A = 1/5/1900 Col B=1/1/1900 and Col C=7/4/1905.

Can concatenate be used to separate strings as well as combine them? I thought I've used concatenate in the past to split out strings and dates in order to rejoin them later later but can't find that function either.

Thank you.

 

[VoG]

Try

Excel Workbook
	A	B	C	D
1	01/05/2012	1	5	2012
Sheet5

 

[AndiH]

I still get the same format in all 3 columns as I did with text to column. Any other ideas?

Thank you!

 

[AndiH]

I changed the format to general and it worked. I'm still curious if concatenate can be used to split and then later join data back together.

Thanks Andi

 

[CWatts]

Change the number format to general.

 

[CWatts]

Looks like we typed at the same time. You can use =DATE(year,month,day) to merge.

EDIT: Another option is if you want to use concatenate, you can merge with DATEVALUE, but DATE is easier IMHO

=DATEVALUE(B6&"/"&C6&"/"&D6)

 

[taurean]

#1 When you do Text to Columns: Be Sure to set the format type to "General"

#2 And the other guess is you tried Peter's formula in cells which were previously formatted as Date (Due to #1)

Sherlock anyone (Bad timing what else? OP found it out)