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]
96 comments
-
Anonymous
commented
"Happy two year anniversary of starting work on this feature"
It's not a feature, it's a bug fix.
-
Anonymous commented
Happy two year anniversary of starting work on this feature
-
D
commented
I use scripts that check BIOS and firmware versions of servers to know which one to update. Those versions are in all kinds of formats (including trailing 0's after what it looks like decimal point) so I have to create special cases often. It also mangles some other data, like some info comes that looks like a time but it not - it is disk info (i.e. "0:1:24").
CSV is a text file so please stop messing up the data. NOTHING should be assumed -
Steven Z
commented
I have 2 words: ZIP CODES
In the northeast US, many zip codes have a leading 0. When I export data from my business system to do a mail merge, etc. I frequently end up with 4 digit zip codes.
-
Scott McGee commented
@Michael Hutchinson - As a default, a software company should not change the user's data, or assume that they know better than the user how the data should be formatted. As a default, the software should allow the user to make a choice, rather than decide for the user.
This issue is a major point of pain for many, many users in business settings and has been such for many, many years, with cumbersome work-a-rounds and gyrations to maintain data in its original format.
-
Anonymous
commented
Michael Hutchinson, if like Open Office, Excel provided a way to format columns in a csv file before opening, then your suggestion would work. But Excel doesn't offer that, so formating as text isn't an option.
In any case, if the data going in to a spreadsheet has leading zeros, then more likely then not, I want them there and Excel should just leave them. The likelihood that I have leading zeros in my data that I don't need, or want stripped, is small, so it doesn't make sense that stripping them is the default. Instead, leaving them as is should be the default and stripping them should be an option.
-
Michael Hutchinson
commented
I disagree with this request. As a default, numerical values should be treated as numerical. If the user wants to format the number as a textual value, i.e. with leading zeros, they should have to explicitly do so (i.e. precede with an apostraphe or format the cell as text). Which is what you currently have to do.
-
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