Does formatting the column as Text after import bring the zeros back?

No. The zeros were discarded when the value was parsed into a number. Changing the display format afterwards changes how the number is drawn, not what it is. You have to re-import with the column typed as text.

Is the ="01234" trick safe?

It works in Excel and it is a formula, not a value. Any tool that reads the file as plain CSV sees the literal text ="01234" including the equals sign and quotes. Use it only when the file will never leave Excel.

Can I pad the zeros back if they are already gone?

Only when the field has a known fixed width, like a five-digit US zip code. Then LPAD restores them deterministically. For a variable-length identifier there is no way to know how many zeros were removed, and guessing produces plausible wrong data.

Why do zip codes in Massachusetts break more than others?

Because US zip codes in New England start with a zero. 02134 becomes 2134 and still looks like a number, so nobody notices until a mail merge fails or a join returns nothing.

What is the most reliable route into Excel?

Convert the CSV to XLSX with the column already typed as text. A workbook carries its types, so Excel opens it without re-guessing, and the zeros survive every subsequent save.

Guide September 3, 2026 by Arif Aslam 6 min read

How to keep leading zeros in a CSV

A zip code of 02134 arrives as 2134. A SKU of 000451 arrives as 451. An employee number of 0027 arrives as 27. The file is fine. The spreadsheet decided those strings were numbers, and numbers do not have leading zeros.

What makes this one dangerous is that the result still looks reasonable. A truncated zip code is a four-digit number, not an obvious error, so it passes review and shows up two systems later as a join that matches nothing.

On this page: why it happens · check what is really in the file · importing as text · the typed workbook route · rebuilding zeros that are gone · exporting without losing them again

Symptom: the zeros are missing the moment the file opens

Cause. CSV stores everything as text and says nothing about types. When Excel opens 02134 it applies a simple rule: if a value parses as a number, it is a number. The mathematical value of 02134 is 2134, so that is what gets stored in the cell. The character sequence in the file is discarded at that moment, not hidden.

This is the same mechanism that produces 1.23E+15 for long IDs. Both come from the eagerness to type a value that was never meant to be arithmetic. Nobody has ever added two zip codes together.

Google Sheets does the same thing. So does a naive pandas.read_csv, which will helpfully infer an int64 column for you. The behavior is not a Microsoft quirk; it is what type inference means.

Check what is really in the file

Before you touch import settings, find out whether the zeros are in the file at all. This single check decides which of the next three sections applies to you.

head -5 yourfile.csv

If you see 02134, the file is intact and you have an import problem, which is fixable. If you see 2134, the damage happened before the file reached you, and no import setting will bring the character back.

Opening the file in a viewer that shows parsed values without reformatting them does the same job with less typing, and it also tells you what type each column was inferred as, which is useful context for the rest of the cleanup.

Fix it: open the CSV and look at the stored values →

Fix: import the column as text

If the file is intact, stop double-clicking it. In Excel, use Data, then From Text/CSV. In the preview dialog choose Transform Data, select the affected column, and set its type to Text before you load. Power Query remembers that choice, so a refresh keeps it.

In Google Sheets, use File, then Import, and set Convert text to numbers, dates and formulas to No. That setting is per-import and it is easy to miss, which is why the problem recurs even for people who know about it.

If you are reading the file in Python, name the columns explicitly rather than relying on inference:

df = pd.read_csv("customers.csv",
                 dtype={"zip": str, "sku": str, "employee_id": str})

Fix it: load the file and set the column type yourself →

Fix: give Excel a typed workbook instead

The most reliable route is to stop asking Excel to interpret a CSV at all. An XLSX file stores a type per cell, so there is nothing to guess. Convert once and the question never comes up again, including on every future save.

Our CSV to Excel converter looks at each column's values and keeps a column as text when they carry leading zeros, rather than typing it as a number and then trying to format the zeros back on. That distinction is the whole point: a number formatted with a leading zero is still a number, and the next tool that reads the cell gets 2134.

Fix it: CSV to Excel, leading zeros preserved →

Fix: rebuilding zeros that are already gone

This only works for fixed-width identifiers. A US zip code is always five characters, so a four-character value is unambiguously missing exactly one zero. Pad it:

SELECT LPAD(CAST(zip AS VARCHAR), 5, '0') AS zip
FROM data;

The same works in Excel with =TEXT(A2,"00000"), and in Python with df["zip"].astype(str).str.zfill(5).

For anything without a fixed width, stop. If SKUs in your catalog range from four to nine characters, a five-character SKU might be complete or might be missing one zero, two zeros or four, and there is no way to tell from the value. Padding it produces data that looks right and is wrong, which is worse than data that is visibly broken. Get a fresh export.

There is a middle case worth mentioning. If you have a second file that still has the intact identifiers, you can sometimes recover by joining on a normalized form of the key: strip leading zeros from both sides, join, and take the intact value.

Fix it: join against a file that still has the real identifiers →

Fix: exporting without losing them again

You have clean data with the zeros intact. Now do not throw them away on the way out.

Export to XLSX, not CSV, when a person will open it. Types survive.
Export to Parquet when a pipeline will read it. Types survive and the file is smaller.
If it has to be CSV, tell the recipient the column is text. There is no way to encode that in the file, so it has to be a human message.

The one thing I would avoid is the ="01234" formula trick. It survives Excel and nothing else. Every other reader gets the literal characters including the equals sign, and now you have a different data quality problem in a different system.

Fix it: export to Parquet and carry the types with the data →