Maintain leading zeroes when entering values in cells
There should be an application setting OR paste recovery option OR cell format to retain leading zeroes when entering values in cells (either manually or through another means like CSV import).
(2015-11-05 Dan [MS] Updated idea title and description to broaden this issue from maintaining leading zeros on CSV data import to maintaining them on any cell entry.)
Anonymous
shared this idea
Hi There
Thanks for the discussion and interest around this. To help us with prioritization, please complete this brief survey:
Please note this is the same survey as seen in the UserVoice request: “Stop Excel from changing large numbers to scientific notation….” so you don’t need to do it twice if you voted on both items.
Best, Urmi [MS XL]
67 comments
-
Anonymous
commented
for phone numbers, add a space and it recognises it as text and keeps leading zeros, (i know this won't cure the problem, but handy for individual cell data entry...
-
corey
commented
wayyy back in the day (excel 5? 97?) the data importer worked much more like in Access. Iirc, it was possible to define import specs and use those. I'm sure someone decided to make this all 'simpler', but its been a pain point for some time. At least one can sort of do the same with the text to columns functionality (if your text imported/pasted right...).
at least Powerquery/PowerBI makes more useful observations and formatting/styles when it imports data, if there is schema info available from the source.
So maybe PowerQuery/PowerBI is a mire right way to hold Excel, as it were... -
Russell Zager
commented
Serious question: what is the point of the survey? You know what the issues are and the survey isn't scientifically sound.
-
Thanks Brin for letting us know. The survey issue is fixed.
Thanks, Urmi [MS XL]
-
Brin
commented
RE: The Survey sent out 1/12/18
The 3rd question format is incorrect for the ask. You say to check all that apply but we can only select a single radiobox, instead of the multiple checkboxes.
-
H Dum
commented
@Scott McGee I have Office 365 on my Desktop Excel 2016 version 1711, on a computer that is just barely 2 months old, Nov 2017, and it does not do leave leading zero's. It is Office 365 personal, maybe only home version. Why Microsoft would include it in one version and not another, whether Office 365 or Office home and student which is not even $4 less than 365 personal bare basic programs and no apps, nor in office online, is ludicrous.
When I choose I want Excel to handle numbers as text as the description/comment is "Text format cells are treated as text even when a number is in a cell. The cell is displayed exactly as entered."
Deleting a leading zero is not exactly as entered.
When I'm trying to track 10 digit identification/account/record numbers or 8 digit identification/account/record, and I have clients that have had their accounts 40+ years and they now have leading zeros because when they were issued in both situations some were only 6 digits, tracking those accounts is a nightmare as some are identical other than double zero's at the end, or to figure out if there's a data entry error when new accounts have been added.
Microsoft unfortunately is the industry standard now, I could make a tracking 'spreadsheet' with far more ease using DOS in the 90's. No automatically changes, it's my data, but I don't have control of it using a spreadsheet, particularly Excel. -
Scott McGee commented
@William Roberts - this already is done, but you have to have Office 365 to get it. If you have a one-time-fee license, too bad, so sad.
-
William Roberts
commented
It would be nice to save Pivot Table preferences. I almost always use the Classic View and turn off Row Totals. Currently I have to open Settings and make this change at least 10 times a day when I create a new Pivot Table.
-
Tony Apfelbeck
commented
Also related to this - stop mangling things like account numbers and phone numbers that are 9+ digits in sequence and displaying them in scientific notation, rounded! This ruins all of that data the moment it's opened in Excel unless you go to extremes to work around it.
-
Louie Hannen
commented
@Scott McGee - The Excel team did an as me anything on Reddit the other day. This was mentioned as a pain point, and the team linked here and said to vote for this. It's been a thorn in my side for more than a decade now, as it has many others. Users shouldn't have to accept that their data is going to be automatically formatted by dropping portions of it without their say-so. That's especially true when opening a file which has data in it (such as csv). Maybe such data could be flagged with suggested changes, but changing it automatically is a bad idea.
-
Not sure why this all of a sudden is getting so many hits, but I *DO* hope there is some listening now, two years later!
The bigger issue is ASSUMING you know what I need/want done with any kind of data I'm bringing in from elsewhere. Best practice IMO is to assume NOTHING and offer the user a choice on how to handle the data, ideally with an option for the user to SET the default.
I frequently copy/paste data from SQL to Excel. This is convenient, quick, and relatively easy. But it could be better!
-
Hayden
commented
I manage an ecommerce store and dropping of leading zeros and the rounding of numbers in CSVs makes working with SKUs and UPCs a nightmare
-
Gustavo Herrera
commented
I would definitely love to see this in a future release. It would be very helpful when dealing with Zip/Postcodes
-
Louie Hannen
commented
Excel should not perform automatic operations which change the contents of an existing .csv file. It should never have happened in the first place. Unless the user opts in to particular algorithms, user entered AND pre-existing data should be treated as sacrosanct, with the application taking steps to ensure it is retained as-is.
-
Anonymous
commented
its been an issue even back in 2006. come on MS, don't change our data!!!
-
Anonymous
commented
Dooooo iiiiiittttttt
-
Anonymous
commented
+1 +1 +1 +1 +1 (..to infinity)
-
Hermy Dermy
commented
Absolutely would love to see this.
-
Anonymous
commented
Please also make it an option to disable the automatic application of formulas when you open a csv in Excel. We receive a lot of csv files that contain IDs that start with a leading dash and every single time you open one Excel tries to convert them into a formula, which of course errors.
Yes, you can import a csv file and force those columns to text but that's a real time suck and annoyance when you're opening anywhere from 50-100 of them a day.
-
Anonymous
commented
Would be a greatly appreciated feature. Please implement this!