Excel 2010 mysterious auto-formatting memory

[erichards]

I am trying to complete an exercise that involves copying a table of data across from a pdf (I only have Adobe Reader). There are no "Special Paste" options so it pastes in multiple rows but a single column. To deal with this I use the "Text to Columns" button using spaces and commas as delimiters.

This all works fine, but then a strange formatting had been applied to the dates (which were originally in mm/dd format) such that the dates for the first 12 days of the month are displayed the wrong way round. Once it gets to the 13th and realises there is no 13th month, it displays the date correctly...but leaves the previous cells displayed incorrectly, eg:

04-Oct
04-Nov
04-Dec
Apr-13
Apr-14
Apr-15

After the text to column conversion has been completed, there seems to be no way of undoing this formatting, changing the formatting to general gives:

40820
40851
40881
41365
41730
42095

Which is nonsense as compared to the original data in the pdf:

4/10
4/11
4/12
4/13
4/14
4/15

This problem can be solved by changing the settings during the text to column conversion and selecting MDY as the date format, but this is where I ran into my main problem.

Deleting the old data, and copying the original data again from the pdf (even into a fresh sheet), Excel automatically performs the text to column conversion. I find this weird as it is obviously linking to the document and trying to help you based on how you formatted the data before...but I find it a bit disturbing privacy-wise. There is no indication that the formatting has been applied and it cannot be reversed. The irritating thing is that it appears to apply the default settings when it converts the data, meaning the dates are screwed up again.

The same thing happens when I paste data from the same document (but say a different month's figures) that I haven't pasted before. It doesn't matter whether I paste it as Unicode or Text (which are the only two options I have). The only way to make excel forget is to open up a new workbook for each month.

Any ideas what is going on?

[shg]

Welcome to the forum.

Excel retains the last TTC settings, as you've found.

You can reset them manually by doing TTC on an empty cell, tick Delimited, and untick all the delimiters.

[erichards]

Thanks for your reply. The point is that in this scenario it doesn't retain the settings. I'm not even clicking the TTC button, it is doing it automatically whenever I copy data (well, essentially just 'text' from a pdf) from the same pdf file. You can't re-do the TTC as it is already split into columns, albeit incorrectly as described in my original post. I'm fairly confident this issue is unique to 2010.

[shg]

Right.

After you do TTC, Excel will do TTC automatically again when you paste, using the previous settings. If it does it in 2010, it means it's unchanged since at least 2002/2003/2007.

[erichards]

It is not using the previous settings, it is using the default settings. Also in 2010 you cannot do TTC on an empty cell (error message: no data was selected to parse)