Why Excel removes leading zeros from CSV files

You export a CSV, open it to check it, and a column of identifiers that read 00123 now reads 123. Nothing you did caused it — the damage happened while the file was opening. The reassuring part is that the file on disk is usually still correct at that moment. The unforgiving part is what happens if you save.

1. What the spreadsheet is actually doing

A CSV carries no types. It is a text file, and every value in it is characters — there is nothing in the format to say whether 00123 is a number, a customer ID, or a piece of text that happens to be digits. So a spreadsheet has to guess a type for each column as it opens the file, and a column of digits looks like numbers.

Once it has decided the column is numeric, 00123 becomes the number one hundred and twenty-three. Leading zeros carry no arithmetic meaning — 00123 and 123 are the same quantity — so they are discarded as redundant formatting rather than deleted as data.

That reasoning is sound for a quantity and wrong for an identifier. The distinction the spreadsheet cannot make is that a customer ID is not a number at all; it is a string of digits with a fixed width, and the width is part of the datum.

2. Which columns this happens to

Every column of digit strings that is not really a number:

All five are digit strings. None of them is a quantity you would ever do arithmetic on.

In Australia this falls hardest on postcodes, and the numbers are specific: 34 Australian postcodes begin with a zero and 115,039 addresses sit at them, almost all in the Northern Territory's 0800–0899 band. Every one of those rows is one a spreadsheet will quietly rewrite, and a postcode that arrives as 800 will not match anything afterwards.

3. Why formatting the column as Text afterwards does not help

This is the step that catches people out, because it looks like it should work. You select the column, set its format to Text, and the values stay 800.

The reason is that formatting controls how a value is displayed, not what it is. By the time you change the format, the cell no longer contains the text "0800" — it contains the number 800, and the original characters are gone from the workbook entirely. Formatting a number as text does not reconstruct digits that were discarded on import.

The same applies to a custom format of 0000. That displays the number 800 as "0800" on screen, which is genuinely useful for reading, but the underlying value is still the number and an export will write whatever the cell contains rather than what it showed.

4. The other version of the same bug

Long digit strings get a different and more obvious form of the same treatment:

The second is the worse of the two, because it still looks like a number of the right length.

A spreadsheet stores numbers with fifteen significant digits. A sixteen-digit card or account number exceeds that, so the trailing digits are replaced with zeros — and unlike scientific notation, which is visibly odd, this produces a value that looks entirely normal and is wrong.

5. Opening a CSV without the damage

The fix is to declare the type yourself rather than let the file be guessed at. Do not double-click the CSV. In Excel, use Data → From Text/CSV, and in the preview that appears, set the affected columns to Text before loading — that is the only moment the choice is offered.

In Google Sheets the equivalent is File → Import, with "Convert text to numbers, dates and formulas" switched off.

If you only need to look at the file rather than edit it, opening it in a text editor shows you what is actually there, with no type inference between you and the data.

6. If it has already happened

Work out whether you still have the original. If the export is repeatable, take it again and import it properly — that is always better than repairing.

If the original is gone, a zero can sometimes be re-derived, but only where the correct width is genuinely known:

An Australian postcode is always four digits, so that recovers a column of them. A customer ID is only recoverable this way if every ID in the system is the same length, which is worth confirming rather than assuming — pad to the wrong width and you have replaced a visible error with an invisible one.

Where the width varies, the information is not recoverable from the file. 123 could have been 00123 or 000123, and nothing in the column can distinguish them. That case needs the source system.

Common questions

Is the CSV itself damaged? Not by opening it. The file on disk still holds the original characters until you save over it, which is why the safest response to noticing this is to close without saving.

Does this happen in Google Sheets too? Yes. Any tool that infers types from a typeless file has the same problem; the import settings differ but the cause does not.

Would XLSX avoid it? Partly. An XLSX stores a type per cell, so a postcode saved as text stays text — but that only helps if the file was an XLSX when the value was written. Converting a CSV to XLSX after the zeros are gone preserves the damage.

Why does the column look right in one place and wrong in another? Because you are seeing a display format in one and the stored value in the other. Trust an export or a text editor over what a grid shows you.