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
Thanks very much for your votes. And to those who took the time to fill in the survey, thank you! This is a brief update to let y’all know that we’ve started work on this feature request along with the one on changing numbers to scientific notation.
- Urmi [Msft]
71 comments
-
Robert
commented
It's not just leading zeros and scientific notation, it is also converting inputs to dates or to fractions.
There should be no amendment to the input data. Excel can propose a way to format it, but the exact original should remain in the cell (including leading zeros, /s, Es, or anything else that could change the formatting) until such time as it is edited by the user.
-
Mary Dearing
commented
Because I have so many legacy documents that I continue to use, some are set up with leading zeros, some are not. Please include a feature that lets us set, per each worksheet, or per workbook, how we want leading zeros to be handled. Thanks,
-
Roy
commented
Yes...
1) How can I even choose to do their limp-along format it as text solution when they are stripped before I even see them? Yes, I can close work and begin again and import as text, but I may not know the leading zero issue exists for data going into said column on import until after I've done a serious amount of work. Why should I lose that?
2) Since they can keep track of the zeros when I tell them it is text, they should keep track of them however they do when I tell them it is text — as the default, always. When I apply a number format, they can display it appropriately but keep the full entry (or imported data) in its original condition. It all flows naturally when begun that way.
That would also help an issue I find with general entry in the cell. Say I enter "=14.30+27.51"... presently it is permanently altered to "=14.3+27.51" no matter how much I wish otherwise (I like things human readable and if my data is usually provided with two decimals, I like to see it that way in the cell. Same with function-laden formulas in which a figure is typed... if I put a "0" at the end of the value, it would be helpful to reading and understanding the work if it remained.
And yeah... that forced change to scientific notation... NO.
Keep the entries and data original. If a problem arises, I deal with it rather than have no option at all. That might read differently if their solution solved the least little thing, but it's a bull in a china shop solution so... it's worse than useless.
-
Anonymous
commented
Microsoft doesn't care and would rather break inputs than listen to its users. This has been a problem for decades.
-
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