Forum Discussion
How to get Excel to use Automatic Data Conversion settings
Hello,
I have been losing data unwittingly because Excel is automatically converting my .csv data into dates without telling me. I have specifically deselected all of the options for enabling automatic data conversion, and I have selected the additional option of "when loading a .csv file or similar file, notify me of any automatic data conversions."
However, I just found that analysis downstream is failing because (as an example) 10-92 is being converted to Oct-92. Selecting the cell shows that it is now 10/1/1992. I have noticed also, that unlike the other cells which are "General", this one is "Custom". Converting it to another format changes the value to something else entirely, rather than going back to the original value of 10-92.
I specifically have disabled all of the automatic data conversion.
I cannot add additional characters to cells, as this will likewise cause downstream analysis to fail.
Why is Excel ignoring its settings? Surely there is a way to prevent this?
This is part of a subscription for Microsoft 365 Apps for enterprise.
Microsoft® Excel® for Microsoft 365 MSO (Version 2506 Build 16.0.18925.20076) 64-bit
1 Reply
Consider this as a workaround:
- Import Using Power Query
This gives you full control over how each column is interpreted:
- Go to Data > Get Data > From File > From Text/CSV.
- In the preview window, click Transform Data.
- Set the column type to Text before loading.
- Open the File in Notepad First (I am always use!)
- Open the .csv in Notepad or another text editor.
- Save it with a different extension (e.g., .txt).
- In Excel, use Data > Get External Data > From Text.
- Use the Text Import Wizard to manually set each column to Text.
- Use Excel’s “Text to Columns” Tool
If the file is already open and data has been converted:
- Select the affected column.
- Go to Data > Text to Columns.
- Choose Delimited, then set the column format to Text.
