Format ZIP Codes and IDs with Leading Zeros in Excel

ZIP codes, employee numbers, account codes and part numbers are identifiers that happen to be written in digits, and many of them start with a zero. Pass them through a CSV file, a database integer column or a careless read_excel, and 02134 becomes the number 2134. When the report is written to Excel, there are two ways to get the zero back: store the code as text, or store the number and apply a number format that pads it. Both look the same in the cell, and both are right in different situations. This page, part of Applying Number and Date Formats in Excel, shows both with pandas, openpyxl and xlsxwriter.

For the reading side — stopping pandas from dropping the zeros in the first place — see Keep Leading Zeros When Reading Excel with pandas.

Prerequisites

pip install pandas openpyxl xlsxwriter

customers.csv has ZIP codes and employee numbers that were saved as plain numbers, so 02134 is now 2134 and 07030 is 7030. One customer has a 9-digit ZIP+4 code.

Step 1 — Store codes as text with zfill

When a code is an identifier — nobody adds ZIP codes together — text is the robust choice. Pad each value to its fixed width with str.zfill, and pandas writes strings as text cells:

import pandas as pd

df = pd.read_csv("customers.csv")
print(df.dtypes.to_dict())

out = df.copy()
out["zip"] = out["zip"].astype(str).str.zfill(5)
out["employee"] = out["employee"].astype(str).str.zfill(6)
out.to_excel("codes_as_text.xlsx", index=False)

from openpyxl import load_workbook
ws = load_workbook("codes_as_text.xlsx").active
print([(ws.cell(r, 2).value, ws.cell(r, 2).data_type) for r in range(2, 6)])

The cells hold the strings 02134, 10001, 07030 — data type s, text. zfill pads to at least the width, so the 9-digit ZIP+4 stays as 21340042, which is not how ZIP+4 is written; the next steps handle it. Text codes show a small green triangle in Excel, the "number stored as text" warning, which Fix "Number Stored as Text" Warnings in Excel Output shows how to suppress. For template columns that people will type codes into, also set the number format to @ — Excel's Text format — on the empty cells: a code typed into a Text-formatted cell is stored as typed, zeros included, instead of being converted to a number the moment Enter is pressed.

Step 2 — Keep numbers and apply a padding format

When the column must stay numeric — a system that imports it expects numbers, or it is sorted numerically — write the numbers and give the cells a format that pads them. In a number format, 0 is a digit that is always shown, so 00000 shows at least five digits:

with pd.ExcelWriter("codes_as_numbers.xlsx", engine="xlsxwriter") as writer:
    df.to_excel(writer, index=False, sheet_name="Customers")
    ws = writer.sheets["Customers"]
    zip5 = writer.book.add_format({"num_format": "00000"})
    emp = writer.book.add_format({"num_format": "000000"})
    ws.set_column("B:B", 12, zip5)
    ws.set_column("C:C", 12, emp)

check = load_workbook("codes_as_numbers.xlsx").active
print([(check.cell(r, 2).value, check.cell(r, 2).number_format) for r in range(2, 6)])

The cells hold 2134, 10001, 7030 as numbers and display 02134, 10001, 07030. Reading the file back with pandas gives the numbers, not the padded text — a number format changes only how a value is shown. In openpyxl the equivalent is cell.number_format = "00000" on each cell of the column.

Step 3 — Handle ZIP+4 and mixed-length columns

A 9-digit ZIP+4 code is written 02134-0042. The format 00000-0000 inserts the hyphen as a literal and pads the whole value to nine digits. When one column mixes 5-digit and 9-digit codes, a conditional section picks the pattern by value: [<=99999] applies the first format to values up to 99,999 and the second to everything else:

from openpyxl import Workbook

wb = Workbook(); ws = wb.active
ws.append(["customer", "zip"])
for name, code in zip(df["customer"], df["zip"]):
    ws.append([name, int(code)])
for row in ws.iter_rows(min_row=2, min_col=2, max_col=2):
    row[0].number_format = "[<=99999]00000;00000-0000"
wb.save("zip_plus4.xlsx")
print([ws.cell(r, 2).number_format for r in range(2, 3)], [ws.cell(r, 2).value for r in range(2, 6)])

Opened in Excel, the column shows 02134, 10001, 07030 and 02134-0042. A text alternative is to build the string in pandas: pad to 5 or 9 digits and insert the hyphen for the long ones, s.str.zfill(9).str.replace(r"^(\d{5})(\d{4})$", r"\1-\2", regex=True) applied only to values longer than five digits.

The stored values never change; only the display does. That is exactly what makes this approach safe for columns other systems read as numbers, and exactly what makes it unsuitable when someone will copy the displayed code into another application, which receives the stored number.

Step 4 — Add prefixes and patterns without changing the value

Number formats can add literal text, so a stored 42 can display as EMP-000042 while staying a sortable number, and a 10-digit number can display as a phone number. Text in double quotes is shown as it is:

wb = Workbook(); ws = wb.active
ws.append(["employee", "phone"])
ws.append([42, 4155550123])
ws.append([30551, 2125550199])
for r in (2, 3):
    ws.cell(r, 1).number_format = '"EMP-"000000'
    ws.cell(r, 2).number_format = "[<=9999999]###-####;(###) ###-####"
wb.save("patterns.xlsx")
print([(ws.cell(r, 1).value, ws.cell(r, 1).number_format) for r in (2, 3)])

The first column shows EMP-000042 and EMP-030551; the second (415) 555-0123 and (212) 555-0199. The prefix is display only: filtering the column for "EMP-000042" in Excel finds nothing, because the cell contains 42. When people will search or copy the code as written, store the full text instead.

Step 5 — Keep lookups consistent

The choice matters most where sheets meet. A lookup — VLOOKUP, XLOOKUP, a pandas merge — compares stored values, not displayed ones. The text "02134" and the number 2134 formatted as 00000 look identical but never match:

orders = pd.DataFrame({"zip": ["02134", "07030"], "orders": [12, 5]})
as_numbers = df[["customer", "zip"]]
as_text = df[["customer"]].assign(zip=df["zip"].astype(str).str.zfill(5))

try:
    as_numbers.merge(orders, on="zip")
except ValueError as exc:
    print("number vs text:", str(exc)[:80])
print("text vs text:  ", as_text.merge(orders, on="zip").to_dict("records"))

pandas refuses to merge an integer column with a text one, and Excel's lookup simply returns #N/A. Pick one representation for each kind of code across the whole workbook — text is the usual choice for identifiers — and convert at the point data enters the report. Codes longer than 15 digits must be text in any case, because Excel keeps only 15 significant digits of a number; see Fix Long ID Numbers Losing Digits in Excel.

Common pitfalls

• Formatting when the value is already text. Number formats do nothing to text cells; pad the string instead.
• zfill on ZIP+4 codes. It pads to a minimum width only; build the hyphenated form explicitly.
• Expecting the format to change the value. A 00000 format still stores 2134; exports and lookups see 2134.
• Mixing text and numbers across sheets. Lookups fail silently; use one representation.
• Saving to CSV. CSV has no formats; the padded display is lost, and Excel strips zeros again on opening.
• Long IDs as numbers. Beyond 15 digits they are rounded; store them as text.

Performance notes

astype(str).str.zfill() is vectorised and fast for any sheet size. Applying a format per column with xlsxwriter's set_column costs nothing per row; in openpyxl, setting number_format cell by cell is fine for tens of thousands of rows.

Conclusion

For identifiers, store codes as text padded with str.zfill, and suppress the number-as-text warning if it bothers readers. When the column must stay numeric, write numbers and apply 00000-style formats — 00000-0000 for ZIP+4, [<=99999]00000;00000-0000 for mixed lengths, and quoted literals for prefixes. Use the same representation in every sheet that is looked up against, and keep codes longer than 15 digits as text.

Frequently asked questions

How do I keep leading zeros when writing to Excel with Python? Either write the value as text — "02134" as a string, for example with str.zfill(5) — or write the number 2134 and give the cell the number format 00000. Both display 02134; text is safer for identifiers, the format for values that must stay numeric.

What number format shows a 9-digit ZIP code as ZIP+4? Use 00000-0000, which shows 21340042 as 02134-0042. For a column that mixes 5- and 9-digit codes, the conditional format <=9999900000;00000-0000 picks the right pattern for each value.

Should IDs be stored as text or numbers in Excel? As text, if nobody does arithmetic on them. Text keeps every digit, never switches to scientific notation and is unaffected by number formats. Store them as numbers only when a downstream system requires numeric IDs, and then apply a padding format.

Why does VLOOKUP fail on IDs with leading zeros? Because the two sides have different types: "02134" as text in one table and 2134 as a number with a 00000 format in the other look identical but do not match. Use the same storage choice in every sheet that is looked up against.

Related

• Up one level: Applying Number and Date Formats in Excel — every kind of number format.
• Write Custom Number Format Codes in Excel with Python — the format code syntax.
• Keep Leading Zeros When Reading Excel with pandas — the reading side.
• Merge Excel Data on Multiple Key Columns with pandas — keys that do not match.