Forum Discussion
Disable auto formatting in Excel
Problem
I'm having issues with dates and numbers in particular where an integer number may be converted to scientific notation (non destructive), although not always, and dates are converted to numbers (destructive).
Desired Result
I want to see the exact characters that are in the data file, in my case CSV. I don't want Excel to format, change or convert any data, I want to see the raw data.
What I've found
It seems that you need to use a VBA script to format all values as string, or Text as I believe it is labelled in Excel. To me this indicates a template document is necessary just so you can open a pretty standard file without messing up any of the data. Correct me if I'm wrong though, as I don't have extensive knowledge with VBA.
References
https://support.microsoft.com/en-us/office/undo-automatic-formatting-in-excel-54eba206-110c-445a-89f1-c4eb67a36bd4
https://support.microsoft.com/en-us/office/stop-automatically-changing-numbers-to-dates-452bd2db-cc96-47d1-81e4-72cec11c4ed8
https://www.journalofaccountancy.com/issues/2016/dec/how-to-turn-off-excel-auto-format.html
https://superuser.com/questions/926771/excel-how-to-stop-automatic-destructive-re-formatting-of-fields-on-document-lo
https://superuser.com/questions/307496/how-can-i-set-excel-to-always-import-all-columns-of-csv-files-as-text
2 Replies
Import your CSV with Power Query, set the correct data types and you are done.
Thanks, that does kind of solve the problem it's just seemingly substantially more effort that I believe is necessary. Point taken though that this function may be too edge case for the average consumer. It would seem other people ask about it, but as for the percentage of users that do, I'm not sure.
