Python Excel AutomationPractical guides for developers
How-to
Advanced Data Transformation And CleaningDeep dive

Normalise Postcodes and ZIP Codes with pandas

Clean the postcode column of an international address list from Excel: restore the leading zeros Excel stripped from US ZIP codes and other numeric postcodes, format UK postcodes with the single space in the right place, handle ZIP+4, Dutch and Canadian formats, validate each value against its country's pattern, and list the ones that need a human.
Data cleaningpandas

Postcodes are the field that address lists get wrong most often. Excel helpfully turns the Boston ZIP code 02134 into the number 2134, and the German postcode 01067 into 1067. UK postcodes arrive as sw1a1aa, SW1A 1AA and SW1A1AA, all meaning the same place. A Dutch postcode might be 1012ab or 1012 AB. Mail merges, delivery integrations and duplicate detection all need one correct format per country, and the format depends on the country — so the cleaning has to look at the country column too. This page builds a postcode normaliser with pandas: read the codes as text, restore lost leading zeros, apply each country's formatting rule, validate against its pattern, and report everything that still does not look right. It belongs to Cleaning Excel Data with pandas.

Prerequisites

Bash
pip install pandas openpyxl

customers.xlsx holds thirteen customers in the US, Canada, the UK, Germany, France and the Netherlands. Several postcodes were stored as numbers and lost their leading zeros, UK and Dutch codes are in mixed case with missing or doubled spaces, one US code is a ZIP+4, and two values are genuinely wrong: a four-digit US ZIP and an incomplete UK postcode.

Step 1 — Read postcodes as text

The first rule of postcodes: never let them be numbers. Read the column as text so nothing more is lost:

Python
import pandas as pd

df = pd.read_excel("customers.xlsx", dtype={"Postcode": str})
print(df[["Country", "Postcode"]].to_string(index=False))

With dtype={"Postcode": str}, pandas reads whatever is in each cell as text. That cannot bring back zeros that are already gone — the cells holding the numbers 2134 and 1067 come back as "2134" and "1067" — but it stops the text values 02108-1234 and 69002 being turned into numbers on the way in, and it means the cleaning code always works with strings. Leading zeros in Excel generally are covered in Keep Leading Zeros When Reading Excel with pandas.

Step 2 — Basic clean-up for every country

Before applying country rules, normalise the things that are wrong everywhere: case, surrounding spaces, repeated internal spaces, and a stray .0 from a number that passed through a float:

Python
import re

def basic(code):
    if code is None or pd.isna(code):
        return ""
    code = str(code).strip().upper()
    code = re.sub(r"\.0$", "", code)          # 2134.0 → 2134
    return re.sub(r"\s+", " ", code)

df["pc"] = df["Postcode"].map(basic)
print(df[["Country", "Postcode", "pc"]].to_string(index=False))

Upper case is the standard for every format that contains letters, and collapsing whitespace turns LS1 4AP into LS1 4AP. The .0 rule handles codes that were read as floats somewhere upstream — a common accident when a CSV export goes through another tool first. After this step the remaining work is country-specific.

Step 3 — Country rules: zeros, spaces and hyphens

Each country gets a small function that formats a cleaned code and a pattern that validates the result:

Python
def us(code):
    digits = code.replace(" ", "")
    m = re.fullmatch(r"(\d{3,5})(?:-?(\d{4}))?", digits)
    if not m:
        return code
    zip5 = m.group(1).zfill(5) if len(m.group(1)) >= 4 else m.group(1)
    return zip5 + (f"-{m.group(2)}" if m.group(2) else "")

def gb(code):
    compact = code.replace(" ", "")
    return f"{compact[:-3]} {compact[-3:]}" if len(compact) >= 5 else compact

def nl(code):
    compact = code.replace(" ", "")
    return f"{compact[:4]} {compact[4:]}" if len(compact) == 6 else compact

def ca(code):
    compact = code.replace(" ", "")
    return f"{compact[:3]} {compact[3:]}" if len(compact) == 6 else compact

RULES = {
    "US": (us, r"\d{5}(-\d{4})?"),
    "GB": (gb, r"[A-Z]{1,2}\d[A-Z\d]? \d[A-Z]{2}"),
    "NL": (nl, r"[1-9]\d{3} [A-Z]{2}"),
    "CA": (ca, r"[A-Z]\d[A-Z] \d[A-Z]\d"),
    "DE": (lambda c: c.zfill(5) if c.isdigit() and len(c) == 4 else c, r"\d{5}"),
    "FR": (lambda c: c.zfill(5) if c.isdigit() and len(c) == 4 else c, r"\d{5}"),
}

def normalise(row):
    fmt, pattern = RULES.get(row["Country"], (lambda c: c, r".+"))
    fixed = fmt(row["pc"])
    return pd.Series({"Postcode clean": fixed, "Valid": bool(re.fullmatch(pattern, fixed))})

df[["Postcode clean", "Valid"]] = df.apply(normalise, axis=1)
print(df[["Customer", "Country", "Postcode", "Postcode clean", "Valid"]].to_string(index=False))

US ZIPs that lost a leading zero are padded back to five digits — 2134 becomes 02134 — and ZIP+4 keeps its hyphen. UK postcodes always have their space before the last three characters (the "inward code"), whatever the length of the first part, so sw1a1aa becomes SW1A 1AA and LS1 4AP becomes LS1 4AP. Dutch codes get a space between the four digits and two letters, Canadian codes between the two halves, and German and French codes are padded to five digits — 1067 becomes 01067, Dresden's real postcode. The patterns check the shape of each result; they confirm a postcode is well formed, not that it exists.

Raw postcodes become each country's standard form The US ZIP 2134 is padded to 02134, the UK code sw1a1aa becomes SW1A 1AA with the space before the last three characters, the Dutch 1012ab becomes 1012 AB, the Canadian k1a0b1 becomes K1A 0B1 and the German 1067 becomes 01067. A B 1 Country Postcode 2 US 2134 3 GB sw1a1aa 4 NL 1012ab 5 CA k1a0b1 6 DE 1067 Country Postcode US 02134 GB SW1A 1AA NL 1012 AB CA K1A 0B1 DE 01067 as stored normalised country rule + pattern

Step 4 — Report what is still wrong

The two values that fail validation need a person, not more code:

Python
bad = df.loc[~df["Valid"], ["Customer", "Country", "Postcode", "Postcode clean"]]
print(bad.to_string(index=False))
print(df.groupby("Country")["Valid"].agg(["sum", "count"]).rename(columns={"sum": "valid", "count": "rows"}).to_string())

1120 for a Brooklyn customer is padded to 01120 — a well-formed ZIP code, but one in Massachusetts, not New York — and is flagged here only if you add a state-level check; as it stands the rule accepts it, which is worth knowing. The incomplete UK code M1 1A fails the pattern and is listed. This is the honest limit of format rules: they catch malformed codes, and padding can make a wrong code look right. If accuracy matters, validate against a postcode database or an address API — the UK's Postcode Address File, USPS address validation, or a commercial service — for the rows that matter. A per-country summary of valid rows shows at a glance where the data quality problems are.

Normalised postcodes also pay off as a join key. Matching customers between two systems, looking up delivery zones or sales territories, or spotting duplicate accounts all compare postcodes, and SW1A1AA, sw1a 1aa and SW1A 1AA only match once they are in one canonical form. For joins, some teams also keep a compact version without any spaces in a separate column, which is easier to compare and to index, while the formatted version is used for printing labels and letters.

What format rules can and cannot tell you Format rules restore zeros, fix spacing and case, and catch malformed codes such as an incomplete UK postcode. They cannot tell whether a well-formed code exists or belongs to the right address; a padded ZIP can look valid and be wrong. That needs a postcode database or address API. format rules can restore lost leading zeros fix spaces and case catch malformed codes (M1 1A) format rules cannot confirm the code exists match code to street or state spot a padded but wrong ZIP

Step 5 — Write the codes so Excel keeps them

The last trap is writing the cleaned codes back to Excel, which will strip the zeros again if the cells are numbers. Write them as text with a text format:

Python
from openpyxl import load_workbook

out = df.drop(columns=["pc"]).rename(columns={"Postcode": "Postcode (original)"})
out.to_excel("customers_clean.xlsx", index=False)
wb = load_workbook("customers_clean.xlsx")
ws = wb.active
col = [c.value for c in ws[1]].index("Postcode clean") + 1
for r in range(2, ws.max_row + 1):
    ws.cell(r, col).number_format = "@"
wb.save("customers_clean.xlsx")
check = pd.read_excel("customers_clean.xlsx", dtype={"Postcode clean": str})
print(check.loc[check["Country"] == "US", "Postcode clean"].tolist())

pandas writes Python strings as text cells, so 02134 is stored with its zero; setting the number format to @ (Text) on the column also stops Excel converting a code if someone edits the cell later. Reading the file back confirms the US codes kept their leading zeros. Keeping the original column next to the clean one preserves what the source sent, which helps when a customer queries their address. Formatting codes for display is covered in Format ZIP Codes and IDs with Leading Zeros.

A postcode clean-up pipeline Read postcodes as text, apply basic clean-up for case and spaces, apply each country's formatting rule and validation pattern, report the values that fail for manual review, and write the cleaned codes as text cells so Excel keeps their zeros. Read as text dtype=str Basic clean case, spaces, .0 Country rule zeros, space, pattern Report + write failures, text cells

Common pitfalls

  • Postcodes as numbers. Leading zeros vanish; read and write them as text.
  • One rule for all countries. Format depends on the country; dispatch on the country column.
  • Inserting the UK space after a fixed position. The space always goes before the last three characters.
  • Trusting a well-formed code. Format rules cannot confirm the code exists or matches the address.
  • Writing clean codes back as numbers. Use text cells with the @ format.

Conclusion

To normalise postcodes from Excel, read them as text, upper-case and tidy the spacing, then apply a formatting rule and a validation pattern per country: pad US ZIP, German and French codes back to five digits, put the UK space before the last three characters, and format Dutch and Canadian codes with their single space. Report every value that fails its pattern for a person to check, remember that a well-formed code is not necessarily a correct one, and write the cleaned codes back as text cells so Excel cannot strip their zeros again.

Frequently asked questions

How do I restore leading zeros in ZIP codes with pandas? Read the column as text and pad four-digit US ZIP codes back to five digits with str.zfill(5), keeping any ZIP+4 extension after a hyphen.

How do I format UK postcodes correctly in Python? Remove all spaces, upper-case the code, and insert a single space before the last three characters, which form the inward code.

How can I validate postcodes for different countries? Use a regular expression per country, such as five digits for the US and Germany or the outward and inward code pattern for the UK, and check each code against its country's pattern.

How do I stop Excel removing leading zeros when I save postcodes? Write them as strings, not numbers, and set the column's number format to text with @.

More on data cleaning

More on data cleaning →