Leave the f'ing leading zeros!!
If my data has leading zeros, then I want leading zeros!.... How is that so hard to understand!?!... Make truncating leading zeros an optional setting, not the default.
For those situations needing to import data and preserve the leading 0's, or for that matter, import dates, or NOT import date-like data as dates, I just posted something on the much older and therefore more voted for, but just as ignored:
which I shall paste in a second (though I suggest voting there as well). This will NOT help with entering data or pasting data, only with data import.
I've lucked across the Dead Sea Scrolls of opening a CSV in "the old manner" in the second answer in a Stack Exchange (Super User) answer:
—— Slightly modified, just the last step ——
(This is different from the version of the old wizard that is presented in the Text to Columns feature in that it still works to do the actual importation, not just to break up already imported or existing data. Same tool, just allowed to act at the moment of importation instead of only after the damage is done.)
Basically, go to Options and make sure that in the selections under the Data heading (at the bottom of them in the "Show legacy data import wizards" section and make sure the third one (at least, required for this... but I've always checked them all, eh?) is selected.
Then, to use the old import wizard the way we all remember it working, go to Data in the Ribbon, choose the first choice on it: "Get Data", and choose "Legacy Wizards" from the dropdown menu.
Here's a nasty foolishness on their part: Pick "From Text (Legacy)" from the dropdown new menu. The foolishness comes from how many people won't know that isn't just .TXT files, but several types of such including .CSV files (and, well, literally anything you think you'd like to try this on for that matter). Anyway, pick it and use the file browser that pops up to find your file and select it.
NOW you finally have the old wizard, functioning precisely as we remember it, and BEFORE it opens the CSV file so BEFORE it destroys information forever. Do like you always did, select "Delimited" and move on, make sure "Comma" is a selected delimiter (really, make it the only one to avoid hassles), and then the magical step... click "Next" to get to the step in which you tell Excel how to import the various columns: as General (it will hammer you if it can find a way), Text, Date (for various layouts), and Skip. (If you are using a different delimiter, you can do a couple other things, but they are not germane to CSV importation.)
Finally, click "Finish" and you're... well, almost home free.
Here's where you'll probably diverge from the Super User answer which assumes you want a continuing connection and a Table. If you do, just choose options as you wish, or don't change anything, just click "OK" and you have a nicely imported Table from the CSV which keeps leading 0's and so on (if you marked that/those column/s to import as Text).
If you JUST WANT A FREAKING BLOCK OF IMPORTED DATA, do one last thing: Uncheck the "Add this data to the Data Model" checkbox (just above the lower left corner's "Properties" button). Now your import will truly be like the old days, just a slick import of the CSV's data with you having had the ability to force the importation approach for any column you wished to do so for. Had date-like data but they weren't dates? You marked them Text and now you're a WINNER! Ruined data is your fault only, like it should be!
I always knew Super User was good for something other than providing cowardly passive aggressive sniping at people's answers and being adolescent t*rd-eating Nazi mountebanks drunk on their internet "power"! Thank you Super User!
For the love of every god, yes PLEASE!!!!! Why after 20 years has this never been addressed. It adds so much inefficiency to my daily work day, especially if someone sends me something in excel and doesn't tell me there are leading zeros and I process/load it incorrectly without ever knowing.
I have never met a single person in tech or office life that LIKES that excel truncates leading zeros.
Seriously. We have been asking for them to turn off all auto features for YEARS. They said they'd fix it THREE YEARS AGO
Open office figured it out, why the **** can't Microsoft??
What really sucks is this has been an issue for decades and still not fixed.....
SOme zip codes start with a zero and excel auto drops the 0 by default. this sucks!!!
The real issue is, I do not trust everyone's ability with Power Query, nor is it always the best option. I sometimes have enough issues with them using Excel correctly. But if the data has leading zeros, it should import with those leading zeros without me having to tell it to do so. I have had so many issues historically where people have opened up csv's in excel, made a change and then didn't realize that a zip code would lose it's leading zeros, which caused programs down the line to blow up. Not to mention issues with leading zeros in Account#s, UPCs, etc.
Thanks for the hint, though I use Open Office Calc to open .csv's since it gives me the option of formatting the columns how I want as it opens.
That's not really my point, though. If the data has leading zeros, wherever it's coming from, then I probably want the leading zeros and Excel should just leave them there. It would be far more convenient to make that the default and truncating the leading zeros the formatting option. I'd rather trim data than not have access to it in the first place.
Sean Kennedy commented
Is your data coming from another source? Ie: export to CSV? If you open the CSV in Excel as opposed to a text editor, it will get rid of leading zeros. If you do not open and import through Power Query, you will also be able to avoid.