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]
92 comments
-
Jay Antoney
commented
"started" 2 years ago... Any updates?
-
TK
commented
*Raises hand for this change*
I work on a LOT of files that contain leading zeros in account numbers, UPCs, IDs, Zip Codes, etc... having to come up with work arounds for this issue seems ridiculous when the data exists, but excel is just ignoring it. NOT all data is numeric, some is descriptive and we should be allowed to see our descriptive data as we want to, not as some overly 'helpful' Excel silent import process sees fit.
-
Anonymous
commented
It's been a while since the Excel Team responded. Has there been any action on this?
-
Futski
commented
MS needs to correct Excel such that it follows normal convention for importing and exporting CSV data.
When importing a quoted CSV field, either text or a number w/leading zeroes, then those quoted fields should be formatted as "Text" in Excel. That way you can import numbers containing leading zeros w/o losing the leading zeroes. Conversely, all text formatted cells should be saved as Quoted text in the CSV regardless of if it looks like a number or not.
An extension to this would be a column that has a number format which adds leading zeros could also be saved in the CSV as quoted text with the leading zeros.
This would also solve the problem of importing/exporting cells that look like they could be in Sci Notation; if it's in quotes, then it's text, period bottom line, and it shouldn't be treated as a number, not now, not ever. -
Anonymous
commented
Please redesign Excel so that Excel files can be imported/exported/opened/saved (as Text, CSV, XLSX, or any other format) keeping the numbers in the formats that the users have them in.
Please redesign Excel so that none of the numbers' formats are changed into any other formats, like scientific format or removing leading zeros, without the user consenting to do so. Please also keep the trailing zeros if that is the way that the user has it, that may be the way that the user would prefer to have them.
At least ask the user before importing/exporting/opening/saving if the user would prefer to have the number formats changed.
Thank you. -
Bia
commented
Can we please have an update on where this is at? Thanks.
-
D
commented
I am using CSV files at work as input for scripts. The data is versions of various firmware components. Those scripts use that data to know if system should do an upgrade or not. Effectively, no versions should be assumed to be as #'s (even though most look like it) and some have leading 0's.
We also have another batch of scripts that work with dates and again Excel butchers the date formats.
Overall, CSV files are pure text files - threat those as such -
Daniel
commented
@Anonymous below: if you specifically paste without formatting into a cell set to "text" then it shouldn't display in scientific notation, or at least it doesn't for me. The problem then is that formula don't work in "text" cells!
-
Anonymous
commented
I suggested here having a "literal" format that will display the data as is, with no interpretation. My issue is more that it will convert to scientific notation even if I tell it to format as "text".
Even explicitly formatting as text does not always work once it is loaded.
-
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.
-
Futski
commented
Importing CSV's has always been a very weak point for Excel. It never get's it right. But I do understand why excel strips the leading zeros, because that's the normal convention for displaying numbers. But I too have been burned by Excel ALWAYS converting the data to numbers (if it looks like a number). A good start would be if Excel simply honored a Double-Quote as text and always text, even if the data looked like a number.
I've also been burned by Excel converting my data to Scientific notation. ie. importing "20E3" converts to Scientific Notation ==> 2.00E+03 . Come on MS, what's the deal with that. Do you really think I intended it to be Scientic Notation. Expecially if it's in a column with other text data like "20K3".Best solution would be to ALWAYS popup an import dialog (when opening a CSV) and allow us to select the column format we want. And being that it starts as a CSV, break it down to it's constituent columns for us, not the normal column based import dialog. And while you're at it, let us apply/create a number display format for a column while we're importing it.
I'd also recommend the same dialog for exporting CSV's, so we can specify Double Quotes on a number column if desired. -
Josh Cole
commented
This is especially important for ZIP codes for the eastern US.
-
Anonymous
commented
I see various comments regarding the how the originating data should be reformatted but these all seem to miss the basic point - the data is NOT Microsoft's and the option of leaving the originating data in its un-amended form seems to be being forgotten
-
Roy
commented
Oddly enough, Excel KEEPS the leading 0 (doesn't just display it for the cell, but keeps it, visible in the formula bar and counted by the LEN() function) for a decimal entry:
0.49 for example
LEN() gives 4 and the 0 is retained in the formula bar display.
WHY would it ever do that, rather than simply default a display format of "0.00"? Add a trailing 0 and it strips that...
Yet entering 0095235 strips the leading 0's for real, leaving only LEN()=5 and 95235.
The day when memory was so low and CPU speed was so slow that individual bytes mattered is over. About 25 years ago, certainly by 20 years ago. And keeping that leading 0 with a decimal only entry doesn't even fit that excuse to begin with.
Scientific convention requires that leading 0 with a pure decimal. The engineering folks I work for now seem to despise it. None here see any need for the leading 0's in the other example and I wager scientists wouldn't either... that's a bookkeeper's concern maybe, but who cares about them... So maybe Excel did it once upon a time to satisfy a perceived huge base of scientist using Excel. That would fit, too, the always converting number displays to scientific notation when given the least excuse.
Hard to believe that a huge portion of the 800,000,000 user community is still scientists or day traders. If 20% were scientists, the world would be extraordinarily advanced today, and if that many were day traders, the phrases "financial meltdown" and "new dark age" would be so-2005. Neither is so, so...
(By the way, the "Anonymous" entry just be low this (4-1, "No, Mr. Ford,...) was mine, I just screwed up and posted it before signing back in. The other fellow was "tres"-aggravating.)
-
Anonymous
commented
No, Mr. Ford, it does not.
Many files are opened by Excel without even presenting the data import wizard. The opening process strips the leading zeros before anything is ever presented to you. So you cannot direct anything with no wizard being presented and even opening them ahead of time and copying over to formatted rows doesn't work as they still get stripped before ever being presented to you.
Sure, works fine when you CAN use the wizard, or are coipying and pasting into prepared columns. Unfortunately, that is not AT ALL all there is.
-
Steve Ford
commented
Prevent leading zeroes from being removed simply format a cell or group of cells as text and not a number. Works every time.
-
Roy
commented
@Anonymous:
Indeed you can. WHEN Excel even presents the Data|Import process to you. Some file types, which make up a very great many files being imported, such as CSV, are simply opened as Excel pleases with no opportunity ever for the Data|Import wizard.
Some, like CSV, are changed to scientific notation when they are opened by Excep, before evn doing the import. Just their opening converts the column of data.
Though oddly, Unicode CSV files often DO have the wizard presented.
-
Anonymous
commented
Steven Z, when importing into Excel, in the import process you can change the type for that Field to text instead of General or Number and it will come in how it was saved (with leading zeroes).
-
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,
-
Anonymous
commented
Noted to Anonymous - will add vote there