Why Excel removes leading zeros from a CSV file, and how to keep them

You open a CSV containing ZIP codes like 02134 and Excel shows 2134. Phone numbers lose their leading 0, and long account numbers turn into 1.23457E+15. The file itself is fine; the damage happens when Excel opens it, and it is permanent if you save over the file.

What is actually going on

A CSV file has no column types: every value is just text. When you double-click it, Excel guesses a type for each column, and anything that looks like a number is converted to a real number. Numbers do not have leading zeros, so they are dropped. Excel also keeps only 15 significant digits (Microsoft's specifications page), so a 16-digit number such as a card or account ID has its last digit replaced with zero, and that cannot be undone. If you save, the file is permanently changed.

Check which columns in your file are at risk

Before you open a file in Excel, you can ask which columns contain values that Excel will damage: numbers with a leading zero, or numbers of 16 digits or more.

import csv, re

with open("zips.csv", newline="", encoding="utf-8-sig") as f:
    reader = csv.reader(f)
    header = next(reader)
    rows = list(reader)

for i, name in enumerate(header):
    values = [r[i] for r in rows if i < len(r)]
    zeros = [v for v in values if re.fullmatch(r"0\d+", v)]
    long_ = [v for v in values if re.fullmatch(r"\d{16,}", v)]
    if zeros:
        print(f"{name}: {len(zeros)} value(s) with a leading zero, e.g. {zeros[0]}")
    if long_:
        print(f"{name}: {len(long_)} value(s) of 16+ digits, e.g. {long_[0]}")

ZIP: 1 value(s) with a leading zero, e.g. 02134
Phone: 2 value(s) with a leading zero, e.g. 0044207946000
Account: 2 value(s) of 16+ digits, e.g. 1234567890123456

The Phone and ZIP columns would lose their zeros, and Account would lose its last digit. Columns that are not listed are safe.

Open the file without losing anything

Do not double-click the CSV. Import it and tell Excel which columns are text. The exact menu names vary between Excel versions, and we have not tested every version, so check Microsoft's current documentation if yours differs. In current Microsoft 365 Excel:

1. Open a blank workbook, then choose Data › From Text/CSV and pick the file.
2. In the preview window choose Transform Data, select the affected columns, and set their data type to Text. (Or set Data Type Detection to "Do not detect data types".)
3. Choose Close & Load.

Now the values are text and keep their zeros. If you save the sheet back to CSV, the zeros are written out as they appear.

The apostrophe and formula tricks

You will find advice to write the value as ="02134" or with a leading apostrophe. Both make Excel show the zero, but the file now contains extra characters that every other program (a database import, a script, a mailing tool) will read literally. It also collides with the formula-protection rule in making a CSV safe to open in Excel, which treats a cell starting with = as suspicious. Prefer the import method, and keep the original file untouched.

If you produce the CSV yourself

You cannot make Excel keep the zeros from inside a plain CSV (it has no way to mark a column as text). Either tell recipients to use the import method, or give them an .xlsx file where the column is stored as text. Never let anyone's "fix" overwrite the original CSV.

Last updated 2026-10-27. Code on this page was run on that date and the output shown is the real output. Spreadsheet menu steps are written from Microsoft's and Google's documented behaviour; where we have not run something ourselves, the page says so.