How to globally stop excel date autoformatting

Question

I'm opening a csv file in excel and every time I open it, it changes some fields into dates when they are clearly not dates (they're CAS numbers, not that its important). How do I stop this behavior? I know I can change the column format but this doesn't help when I first open the CSV file.

Solution 1

Unfortunately, there's not much you can do while the file has .csv extension. Excel has special handling for .csv files. There is a way to deal with it, but it involves renaming the files to have some other extension. You can rename .csv file to have extension .txt, for example, then open the .txt file with Excel: this time Excel will ask you to confirm details of the files for import. At the first step, select delimited; at the second step, select comma as the delimiter and double quote for the quote element. Finally, at the next step, you will have the option to specify the column formatting. Just select all columns and select text, for example. Alternatively, select the one column in question and choose number (although this risks getting scientific notation (1.23456E+10) if the numbers are long.

Solution 2

If the files stay in csv format than they cannot retains any formatting choices that you make. So you have two choices:

1. Unformat the columns and save the file to .xls
2. Use the Text Import Wizard each time you wish at access this data. This tool give you the option to specify how the data will be interpreted by Excel.

Comments

[Aleks G]

I think you're missing the point. The OP needs to get correct formatting before he/she can do anything with it.

[fearis]

This was in 2012.. any news about new version of Excel ?

[Aleks G]

Nothing new in the newer excel version.

[reima]

Just how unbelievably stupid this is. Shouldn't the default be number, not date when in Excel?