Feedback by UserVoice

How can we improve Excel for Windows (Desktop Application)?

stop automatically removing leading zeros from cells. they should stay be default as they are NEEDED for the sheet.

stop automatically removing leading zeros from cells. they should stay be default as they are NEEDED for the sheet.

40 votes
Sign in
(thinking…)
Sign in with: Facebook Google
Signed in as (Sign out)

We’ll send you updates on this idea

Anonymous shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

11 comments

Sign in
(thinking…)
Sign in with: Facebook Google
Signed in as (Sign out)
Submitting...
  • Futski commented  ·   ·  Flag as inappropriate

    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.

  • Anonymous commented  ·   ·  Flag as inappropriate

    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  ·   ·  Flag as inappropriate

    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  ·   ·  Flag as inappropriate

    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  ·   ·  Flag as inappropriate

    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  ·   ·  Flag as inappropriate

    @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  ·   ·  Flag as inappropriate

    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).

  • Steven Z commented  ·   ·  Flag as inappropriate

    Issue with exporting csv of contacts from Outlook. Specifically looses the leading zero (0) on zip codes such as 01776.

    Interestingly it does not loose the leading zero with extended zip codes such as 01776-4444

Feedback and Knowledge Base