Text to Columns separates Date into three dates?

[Sprackers]

Hi All,

this is driving me mad.

I have a column of dates format 01/06/2018 for example. I want to separate them into individual columns. when I use Text to Columns they get separated into this

01/06/2018 from this to this below. three columns


[TABLE="width: 516"]
<tbody>[TR]
[TD="align: right"]Jan[/TD]
[TD="align: right"]06-Jan-00[/TD]
[TD="align: right"]10-Jul-05[/TD]
[/TR]
</tbody>[/TABLE]


Columns to text many times and have tried formatting the column all to no avail.

Any ideas chaps?

Phil

 

[decadence]

Have you tried deliminating by / when using text to columns?

 

[Sprackers]

Have you tried d eliminating by / when using text to columns?

Hi yes I did that first. Delimited by / and in the little window it shows and example of what it will look like. In the little window it shows that the date will be split perfectly however when I press finish it splits into three columns , but in each column it shows the details as above message?

I tried to delimit by width and every possible permutation and it just keeps coming up with:

Jan 06- Jan-00 10-Jul-05

 

[Joe4]

I have a column of dates format 01/06/2018 for example. I want to separate them into individual columns. when I use Text to Columns they get separated into this

01/06/2018 from this to this below. three columns


[TABLE="class: cms_table, width: 516"]
<tbody>[TR]
[TD="align: right"]Jan[/TD]
[TD="align: right"]06-Jan-00[/TD]
[TD="align: right"]10-Jul-05[/TD]
[/TR]
</tbody>[/TABLE]

If you got this, then congratulations! That means it actually worked and did separate them correctly.
You just need to change the format of these three columns to General (or some other Number format) to see the data in the format you expect.

To understand what is going on, you need to understand that Excel actually stores dates as numbers, specifically the number of days since 1/0/1900.
So 1 translates to date January 1, 1900
6 translates to January 6, 1900
and 2018 translates to July 10, 1905

So, you see, you are actually getting the right values, you are just looking at them in a Date format instead of a Number format.

 

[Peter_SSs]

What happens if, after the text to columns, you format those result columns as "General"?