Disable auto formatting in Excel

[Joshua Balsillie]

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

Undo automatic formatting in Excel

Stop automatically changing numbers to dates

Microsoft Excel: How to turn off Excel's AutoFormat feature

Excel - how to stop automatic destructive re-formatting of fields on document load [duplicate]

How can I set Excel to always import all columns of CSV files as Text?

[#2]

@Joshua Balsillie 

Import your CSV with Power Query, set the correct data types and you are done.

[#3]

@Detlef Lewin 

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.