Every single problem I've ever had with excel stems from excel trying to be too clever. Nearly all my problems with importing data into it could be solved if excel left everything as is until I asked it to format values a certain way.
We're biased to bash on Microsoft for being "too clever" but maybe we need a reality check by looking at the bigger picture.
Examples of other software not written by Microsoft that also drops the leading zeros and users asking questions on how to preserve them:
- Python Pandas import csv issue with leading zeros: https://stackoverflow.com/questions/13250046/how-to-keep-lea...
- R software import csv issue with leading zeros: https://stackoverflow.com/questions/31411119/r-reading-in-cs...
- Google Sheets issue with leading zeros: https://webapps.stackexchange.com/questions/120835/importdat...
Conclusion: For some compelling reason, we have a bunch of independent programmers who all want to remove leading zeros.
Looking forward to the first time anyone tries to use your excel on a table of numbers and then immediately has to multiply everything by *1 (in a separate table) just to get it back into numbers...
At least you would know what's happening and be in control of it
"Hey, is that a date? I bet that's a date!" - Aaaargh Noooo!
It's user's faults for using it in ways that it was never designed for.
Excel has always been about sticking numbers in boxes and calculating with them.
If you want unmodified string input, input strings into a tool intended to handle them.
Project specifications can be hard. Using 1) .xls files after they were superseded, 2) ANY data transfer method without considering capacity or truncation issues, speaks of incompetence.
People just double click the CSV and complained that it didn't do it correctly. It is the same situation with scientific research data that researchers don't bother to use escape marker or blindly open the file without going through the proper import process. Then they blamed Excel for the that without understanding how Excel works.
Yes, Excel does have their quirks. But there are ways around those quirks, they have thousands of thousands guides out there about Excel. There is no excuses for people to complain about Excel didn't do the way that users want it to do without looking up for information.
Double clicking the CSV should open the data import dialog.
And that's what you have in Excel. What gets displayed is a separate issue.
And no, you don't want to see exactly what you typed in, not in the general case.
And no, I can't believe I am defending Excel!
I believe that's the point, it certainly does NOT need to.
I think it would have been far quicker to just manually write a new column interpreting the dates based on previous/next etc. Instead I spent God knows how long trying to be clever, failing, and being embarrassed that I could not solve this obviously trivial problem.