How to globally stop excel date autoformatting
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.
Answer
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.