Why Excel Deletes the Zeros From Your Product Codes

Excel removes leading zeros because it reads a column like 00123 as a number, and the number 123 has no leading zeros to keep.

A CSV file has no column types. Every value in a CSV is plain text, and Excel decides what each value is at the moment the file opens. A value made only of digits becomes a number, so product codes, postal codes and phone numbers lose their leading zeros, and numbers longer than 15 digits lose their last digits. Nothing in the CSV on disk changes until the file is saved.

Which values lose their zeros, and can they be recovered?

Every identifier written only in digits loses its leading zeros when Excel opens a CSV, and every number longer than 15 digits loses its last digits. A lost leading zero can be recovered from the original CSV; a digit lost past the 15th cannot be recovered from Excel at all.

A custom number format such as 00000 (Ctrl+1, then Custom) makes a ZIP code display as 08540 again, but the cell still holds the number 8540, so a lookup against the code 08540 still fails. A custom format also cannot help a column of codes with different lengths, such as 0123 and 00456, because one format adds the same number of zeros to every value.

Are the zeros gone from the CSV file?

The CSV on disk is still correct. Excel only changed what it displays. The damage becomes permanent the moment you save over the file — so close it without saving, and open the original again.

Saving writes what Excel shows, not what the CSV held. In the test above, saving the CSV from Excel turned 00123 into 123 and the 16-digit card number into 4.53202E+15, which keeps 6 of the 16 digits. A copy of the CSV saved before Excel opened it is the only way back after that.

Open the CSV again with the code columns as text

To open a CSV without losing leading zeros, import it through Power Query and set the code columns to Text before Excel loads them. With Excel's default settings, double-clicking the file converts the columns to numbers every time.

1. Open a blank workbook. On the Data tab, click From Text/CSV and select the file.
2. In the preview window, click Transform Data.
3. Select the code column, or Ctrl+click several columns, then choose Home → Data Type → Text.
4. When Excel asks how to change the column type, choose Replace current. Adding a new step instead would convert 123 to text after the zeros are already gone.
5. Click Close & Load.

The older Text Import Wizard ignores the column formats you choose when the file name ends in .csv. In the same test, a CSV imported with every column set to Text still showed 123; the identical file renamed to .txt kept 00123 and all 20 digits of the account number. Rename the file to .txt before using the wizard.

Excel for Microsoft 365 and Excel 2024 also have Automatic Data Conversion settings under File → Options → Data that stop Excel removing leading zeros and cutting long numbers. Those settings change one computer only: a colleague who opens the same CSV on another computer still loses the zeros.

Why does Excel change the last digits of long numbers to zero?

Excel changes the last digits of long numbers to zero because Excel stores at most 15 significant digits, and every digit after the 15th becomes 0 when Excel converts the value to a number. Excel does this on import, the moment the CSV opens, and shows no warning.

You had:      4532015112830366
Excel stores: 4532015112830360
Excel shows:  4.53202E+15

A missing leading zero is easy to spot, because the code is one digit short. A number cut at 15 digits is not: 4532015112830360 has the right length and looks valid, so the error passes a check by eye and only surfaces when the number fails to match, as a payment that cannot be reconciled or an order that cannot be found.

Exports with numbers longer than 15 digits include:

• Card numbers. A 16-digit card number keeps 15 digits, and the 16th becomes 0.
• Bank account numbers exported as digits. A 20-digit account number keeps 15 digits and ends in five zeros.
• Long order and transaction IDs. Post IDs from X (formerly Twitter) have 19 digits, so the last 4 become 0.
• IBANs split into parts. A full IBAN starts with two letters, such as FR76, so Excel keeps a full IBAN as text. The digits of an IBAN exported in a column of their own are cut like any other long number.

Formatting the column afterwards cannot bring the digits back, because Excel never stored them. Microsoft's own documentation says a number with 16 or more digits must be stored as text. The import steps above do that: a column set to Text before loading keeps every digit.

What if the same file arrives every week?

If one file is the whole problem, the import steps above solve it. If a system sends you a file like this every week, re-doing those steps every time is the real cost.

When the weekly file always has the same name and sits in the same folder, the Power Query import can be refreshed with Data → Refresh All instead of rebuilt. When the file name, the sender or the columns change from week to week, the steps start again.

AnyToExcel never turns a value that starts with 0 followed by another digit, or a value with more than 15 digits, into a number, so the codes and long IDs in a CSV reach the .xlsx as text with every digit kept. One limit: a code column where none of the first 200 values starts with 0, such as 123 and 456, becomes a number column, with every digit kept but no zeros to add back. Upload the file to the CSV to Excel converter; no account is needed for files up to 4 MB.