How-to

Getting Started With Python Excel AutomationDeep dive

Keep Leading Zeros When Reading Excel with pandas

Customer IDs, ZIP codes and phone numbers lose their leading zeros in read_excel. Fix it for text cells with dtype, and for numbers that only look padded because of a 00000 format, by reading the format with openpyxl.

Reading Excel files Data cleaning pandas

Customer 00123 becomes 123, ZIP code 02134 becomes 2134, and a join against the CRM export quietly matches nothing. Losing leading zeros is one of the most common silent data bugs in Excel pipelines, and it has two different causes that look identical on screen. Sometimes the cell really contains the text 00123 and pandas converts it to a number. Sometimes the cell contains the number 2134 and only a 00000 number format makes Excel show 02134. The fixes differ. This guide, part of Reading Excel Files with Pandas, covers both and how to tell which one you have.

Prerequisites

Bash

pip install pandas openpyxl

This workbook has both kinds: CustomerID and Phone stored as text with zeros, and Zip and Account stored as numbers displayed with zero-padded formats:

Python

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = "Customers"
ws.append(["CustomerID", "Zip", "Phone", "Account"])
ws.append(["00123", 2134, "0612345678", 42])
ws.append(["00456", 501, "0687654321", 7])
for row in (2, 3):
    ws.cell(row, 2).number_format = "00000"
    ws.cell(row, 4).number_format = "000000"
wb.save("customers.xlsx")

Step 1 — See the problem

A default read converts every column to integers, including the two that were text in the workbook. pandas' read_excel runs its values through the same type inference used for CSV files, and a column where every value parses as a number becomes numeric.

Python

import pandas as pd

raw = pd.read_excel("customers.xlsx", sheet_name="Customers")
print(raw)
print(raw.dtypes)
#    CustomerID   Zip      Phone  Account
# 0         123  2134  612345678       42
# 1         456   501  687654321        7

Nothing here raises an error, which is exactly what makes it dangerous. The IDs are still unique, the phone numbers still look like phone numbers, and the damage only appears when the values are joined, looked up or written back.

Step 2 — Keep text cells as text with dtype

For values stored as text, tell pandas not to infer. dtype=str applies to every column; a dictionary targets specific ones, which keeps real numeric columns numeric.

Python

by_column = pd.read_excel("customers.xlsx", sheet_name="Customers",
                          dtype={"CustomerID": str, "Phone": str})
print(by_column)
#   CustomerID   Zip       Phone  Account
# 0      00123  2134  0612345678       42

Identifier columns should essentially always be read as strings, zeros or not. An ID is a label, not a quantity: nobody adds customer numbers together, and treating them as text also protects against long numeric IDs losing precision as floats. The general rules for choosing dtypes on read are in How to Read Excel Files With Pandas: A Step-by-Step Guide.

Step 3 — Recognise zeros that only exist in the format

Zip is still 2134 even with dtype=str, because the cell's value genuinely is the number 2134. The zero is display formatting, like a currency symbol. Check with openpyxl: a cell's data_type is "s" for text and "n" for numbers, and number_format shows the pattern.

Python

from openpyxl import load_workbook

sheet = load_workbook("customers.xlsx")["Customers"]
for ref in ("A2", "B2", "D2"):
    cell = sheet[ref]
    print(ref, repr(cell.value), cell.data_type, cell.number_format)
# A2 '00123' s General
# B2 2134 n 00000
# D2 42 n 000000

This check is worth running once on any unfamiliar workbook before choosing a fix. It also explains a common confusion: someone insists the file "has the zeros" because they can see them, while every script reads the number without them.

Step 4 — Restore zeros from the number format

A format made only of zeros — 00000, 000000 — means "pad to this many digits". Read each column's format from the first data row, and pad the values with zfill to that width. This works whatever the width, so one function handles ZIP codes, account numbers and employee IDs alike.

Python

import re

def zero_pad_widths(path: str, sheet_name: str, header_row: int = 1) -> dict[str, int]:
    ws = load_workbook(path, read_only=True)[sheet_name]
    rows = ws.iter_rows(min_row=header_row, max_row=header_row + 1)
    header, first = next(rows), next(rows)
    widths = {}
    for head, cell in zip(header, first):
        if cell.data_type == "n" and re.fullmatch(r"0+", cell.number_format or ""):
            widths[head.value] = len(cell.number_format)
    return widths

widths = zero_pad_widths("customers.xlsx", "Customers")
print(widths)                                        # {'Zip': 5, 'Account': 6}

customers = pd.read_excel("customers.xlsx", sheet_name="Customers",
                          dtype={"CustomerID": str, "Phone": str})
for column, width in widths.items():
    customers[column] = customers[column].astype("Int64").astype(str).str.zfill(width)
print(customers)

Converting through the nullable Int64 type first avoids a trailing .0 when a column has blank cells and pandas stored it as float. If the format is not uniform down the column — a mix of padded and unpadded cells — check more than the first row, or pad from a known width for that field instead.

Step 5 — Use converters for fixed, known formats

When the width is part of the data's definition — US ZIP codes are five digits, a company's employee IDs are eight — a converter applies it during the read without looking at formats at all. Converters receive each raw cell value and return what should go into the frame.

Python

def five_digit_zip(value) -> str:
    return f"{int(value):05d}" if pd.notna(value) and str(value).strip() else ""

zips = pd.read_excel("customers.xlsx", sheet_name="Customers", converters={"Zip": five_digit_zip})
print(zips["Zip"].tolist())                          # ['02134', '00501']

Converters handle both causes at once — a text "02134" and a number 2134 both come out as "02134" — which makes them robust for files assembled by several people with different habits. The trade-off is that they are slower than a dtype, because they run in Python for every cell.

Step 6 — Keep the zeros when writing back

The same values can lose their zeros again on the way out. A string column written with to_excel is stored as text and keeps its zeros, but Excel then marks those cells with the "number stored as text" warning triangle. When the output must stay numeric — for a system that expects numbers — write numbers and a padded format instead.

Python

with pd.ExcelWriter("customers_out.xlsx", engine="openpyxl") as writer:
    customers.to_excel(writer, sheet_name="Customers", index=False)
    out = writer.sheets["Customers"]
    for cell in out["B"][1:]:
        cell.value = int(cell.value)                 # store the number…
        cell.number_format = "00000"                 # …and show it padded

check = load_workbook("customers_out.xlsx")["Customers"]
print(check["A2"].value, check["A2"].data_type, check["B2"].value, check["B2"].number_format)

Identifiers such as CustomerID belong as text; codes that downstream systems treat as numbers can be written as numbers with a format. Silencing the warning triangle for text IDs is covered in Fix "Number Stored as Text" Warnings in Excel Output.

Step 7 — Normalise keys before joining two sources

The most expensive consequence of lost zeros is a join that silently drops rows: the CRM export has 00123, the finance workbook has 123, and a merge returns nothing for that customer. Normalise identifier columns on both sides to one canonical form before merging, and check the match rate so a mismatch is loud.

Python

def canonical_id(series: pd.Series, width: int) -> pd.Series:
    text = series.astype("string").str.strip().str.replace(r"\.0$", "", regex=True)
    return text.str.zfill(width)

crm = pd.DataFrame({"CustomerID": ["00123", "00456", "00789"], "Segment": ["A", "B", "A"]})
finance = pd.DataFrame({"CustomerID": [123, 456, 999], "Balance": [250.0, 90.0, 12.5]})

crm["key"] = canonical_id(crm["CustomerID"], 5)
finance["key"] = canonical_id(finance["CustomerID"], 5)
joined = crm.merge(finance[["key", "Balance"]], on="key", how="left", indicator=True)

match_rate = (joined["_merge"] == "both").mean()
print(joined[["CustomerID", "Balance", "_merge"]])
print(f"matched {match_rate:.0%} of CRM customers")

The str.replace(r"\.0$", "") handles identifiers that passed through a float somewhere upstream and arrive as "123.0". indicator=True adds a _merge column that makes unmatched rows visible, and logging the match rate on every run turns a formatting regression in either source into an obvious drop in a number you already watch. More merge diagnostics are in Find Rows in One Excel File Missing from Another.

Common pitfalls

Symptom	Cause	Fix
`00123` read as `123`	Type inference on a text column	`dtype={"CustomerID": str}`
Still `2134` with `dtype=str`	Zero exists only in the `00000` format	Pad from the format width, or a converter
Padded values end in `.0`	Column became float because of blanks	Convert through `Int64` before `str`
Long IDs show as `1.23457E+15`	Numeric ID stored as a float	Read as `str`; store IDs as text
Zeros lost after a CSV step	CSV written and re-read without dtype	Pass `dtype=str` when reading the CSV too
Joins match nothing	One side padded, the other not	Normalise both sides with the same `zfill`

Performance and scale notes

dtype is applied during parsing and costs nothing extra. The format check with openpyxl reads only the header and first data row in read-only mode, so it stays fast on large files. Converters are the slow option — a Python call per cell — which is noticeable above a few hundred thousand rows; for large files, read with dtype=str and apply a vectorised str.zfill afterwards, which does the same work far faster.

Conclusion

Leading zeros disappear either because pandas infers numbers from text or because the zeros were never data, only a 00000 format. Check which with openpyxl's data_type and number_format. Read text identifiers with dtype=str, restore format-only zeros with zfill to the format's width, and use converters when the width is fixed by definition. Treat identifiers as strings throughout the pipeline, normalise both sides before joins, and decide deliberately between text and formatted numbers when writing back.

Frequently asked questions

Why does pandas remove leading zeros from Excel? Either the column held numeric-looking text that pandas inferred as numbers, or the cells were numbers displayed with a zero-padded format. dtype=str fixes the first; padding from the format fixes the second.

Does dtype=str affect dates and real numbers? Yes, if applied to the whole sheet — dates become strings too. Pass a dictionary with only the identifier columns to keep other columns typed normally.

How do I keep leading zeros in phone numbers with a plus sign? Numbers like +44 are text in Excel, so dtype=str keeps them exactly. Store phone numbers as text end to end rather than as numbers.

Can I fix the zeros in the Excel file itself? Yes. With openpyxl, convert numeric cells to padded strings, or keep the numbers and apply a 00000 number format. Which is right depends on whether downstream tools expect text or numbers.

Up one level: Reading Excel Files with Pandas — every read_excel option that shapes your data.
Read Specific Columns From Excel With Pandas — combining usecols with dtype.
Check Excel Data Types with pandas — validating dtypes after the read.
Convert Excel Text Columns to Numbers with pandas — the opposite conversion, for real quantities.
Merge Two Excel Files on a Common Column in Python — where mismatched zeros break joins.

More on reading excel files