Split an Address Column into Street, City and Postcode

Addresses often arrive from CRM exports, web forms and old spreadsheets as one cell: Unit 4, Harbour Park, 12 Quay Road, Bristol BS1 4QA or three lines typed with Alt+Enter inside the cell. Mail merges, courier uploads, tax reports and deduplication all need the parts separately — street, city, postcode, country. Address parsing in full generality is a hard problem, but business data is usually more regular than it looks: the postcode has a strict format for each country, the city sits next to it, and everything before that is the street and building. This page splits a mixed address column with pandas using those facts — normalising line breaks and commas, finding the postcode with a country-specific pattern, taking the city from around it, keeping building lines, standardising postcode spacing — and puts anything it cannot split confidently on a review list. It belongs to Cleaning Excel Data with pandas.

Prerequisites

pip install pandas openpyxl

The sample has ten customer addresses from four countries, some on one line with commas, some with line breaks inside the cell, one with the country appended, two missing their postcode. For millions of international addresses with no country column, a statistical parser such as libpostal is the better tool; for business data with a known country, the rules below are transparent and easy to check.

Step 1 — Normalise the separators

import re
import pandas as pd

df = pd.read_excel("addresses.xlsx")
COUNTRY_WORDS = r",?\s*(United Kingdom|UK|USA|United States|Germany|Deutschland|France)\s*$"

def parts_of(address):
    text = re.sub(COUNTRY_WORDS, "", str(address).strip(), flags=re.I)
    pieces = [p.strip() for p in re.split(r"[\n\r]+|,", text) if p.strip()]
    return pieces

df["parts"] = df["Address"].map(parts_of)
print(df[["Customer", "parts"]].head(4).to_string(index=False))

Line breaks typed with Alt+Enter arrive in pandas as \n, and both they and commas mark the boundaries between address parts, so splitting on either gives a list of pieces. A trailing country name is removed first — the country column already holds that, and leaving it in would put "United Kingdom" where the city should be. Empty pieces from doubled commas or blank lines are dropped, and each piece is stripped of spaces, so Bristol BS1 4QA and Bristol BS1 4QA are treated the same. Nothing else is changed at this stage: the original spelling and capitalisation stay, so the review sheet later shows exactly what was typed.

Step 2 — Find the postcode and the city around it

Postcodes are the most reliable anchor, because each country's format is strict. Search the pieces from the end, where postcodes usually are:

POSTCODE = {
    "GB": r"\b([A-Z]{1,2}\d[A-Z\d]?)\s*(\d[A-Z]{2})\b",
    "US": r"\b(\d{5})(?:-(\d{4}))?\b",
    "DE": r"\b(\d{5})\b",
    "FR": r"\b(\d{5})\b",
}

def street_and_extra(pieces):
    """The street is the last piece that starts with a house number; other pieces are extra lines."""
    numbered = [i for i, p in enumerate(pieces) if re.match(r"\d", p)]
    k = numbered[-1] if numbered else len(pieces) - 1
    if k < 0:
        return "", ""
    return pieces[k], ", ".join(p for j, p in enumerate(pieces) if j != k)

def split_address(parts, country):
    pattern = POSTCODE[country]
    for i in range(len(parts) - 1, -1, -1):
        m = re.search(pattern, parts[i], flags=re.I)
        if not m:
            continue
        if country == "GB":
            postcode = f"{m.group(1)} {m.group(2)}".upper()
        elif country == "US":
            postcode = m.group(1) + (f"-{m.group(2)}" if m.group(2) else "")
        else:
            postcode = m.group(1)
        rest = (parts[i][:m.start()] + parts[i][m.end():]).strip(" ,")
        before = parts[:i]
        region = ""
        if country == "US":
            state = re.search(r"\b([A-Z]{2})\s*$", rest)
            if state:
                region, rest = state.group(1), rest[:state.start()].strip()
        if not rest and before:
            rest, before = before[-1], before[:-1]
        street, extra = street_and_extra(before)
        return {"Street": street, "Extra": extra, "City": rest, "Region": region, "Postcode": postcode, "Status": "ok"}
    pieces, region = list(parts), ""
    if country == "US" and pieces and re.fullmatch(r"[A-Z]{2}", pieces[-1]):
        region = pieces.pop()
    city = pieces.pop() if len(pieces) > 1 else ""
    street, extra = street_and_extra(pieces)
    return {"Street": street, "Extra": extra, "City": city, "Region": region, "Postcode": "", "Status": "no postcode found"}

split = pd.DataFrame([split_address(p, c) for p, c in zip(df["parts"], df["Country"])])
result = pd.concat([df[["Customer", "Country"]], split], axis=1)
print(result.to_string(index=False))

The pattern for each country captures the postcode in a standard shape: UK postcodes are an outward code and an inward code, rejoined with one space and upper-cased so nw1 6xe becomes NW1 6XE; US ZIP codes keep their optional ZIP+4 suffix; German and French postcodes are five digits. Whatever shares the postcode's piece is the city — Bristol BS1 4QA, 10117 Berlin, New York, NY 10118 with the state split off — and if the postcode stands alone on its line, as in the London address, the city is the piece next to it. Among the pieces before the city, the street is the last one that starts with a house number — 12 Quay Road, 350 Fifth Avenue — so a suite or flat number written after the street does not take its place; if no piece starts with a number, as with The Old Mill, Station Lane, the last piece is used. Everything else — unit numbers, building names, suites — goes into an Extra column rather than being lost.

Step 3 — Review what the rules could not split

review = result[result["Status"] != "ok"]
print(review[["Customer", "Country", "Street", "City", "Status"]].to_string(index=False))

with pd.ExcelWriter("addresses_split.xlsx", engine="openpyxl") as xw:
    result.to_excel(xw, sheet_name="Split", index=False)
    review.merge(df[["Customer", "Address"]], on="Customer").to_excel(xw, sheet_name="Review", index=False)

Two addresses have no postcode at all: the Didsbury flat and the Austin PO box. The rules still make a sensible best effort — 18 High St as the street with Flat 2 as an extra line and Didsbury as the city; Austin as the city with TX as the state — but mark them for review rather than presenting a guess as a clean split, and the review sheet carries the original address alongside for whoever fixes them. In practice the review list is short once the patterns are right, and it is the honest output: a courier upload with a missing postcode fails anyway, and it is better to find out here. Postcode formats themselves are cleaned more thoroughly in Normalise Postcodes and ZIP Codes with pandas.

Testing the rules on your own data

Address rules are only as good as the examples they were tried on, so build a small test set from your own data before relying on them: twenty or thirty real addresses per country, chosen to include the awkward cases — PO boxes, building names without numbers, addresses with the postcode on its own line, house numbers written after the street name as in much of continental Europe. Write the expected street, city and postcode next to each and run the split against them; every disagreement either improves a rule or becomes a known limitation listed on the review sheet. German and French addresses put the house number after the street name, so the "starts with a number" rule picks them up only because there is a single street line; if your data has building names there too, a pattern for a trailing number is the next rule to add.

Common pitfalls

• Splitting on commas only. Alt+Enter line breaks separate address parts too.
• One postcode pattern for all countries. Use the row's country.
• Leaving the country name in the address. It ends up in the city column.
• Dropping building and unit lines. Keep them in a separate extra column.
• Presenting guesses as results. Flag addresses without a postcode for review.

Conclusion

To split single-cell addresses, normalise Alt+Enter line breaks and commas into pieces, remove a trailing country name, find the postcode with the row's country pattern searching from the end, take the city from the same or the neighbouring piece, the street from the piece before and keep earlier pieces as extra lines. Standardise postcode formatting while you are there, and send addresses with no recognisable postcode to a review sheet together with their original text.

Frequently asked questions

How do I split an address in one Excel cell into columns with Python? Split the text on line breaks and commas, find the postcode with a pattern for the address's country, and take the city and street from the pieces around it.

How do I handle line breaks inside Excel cells in pandas? Alt+Enter line breaks are read as \n characters; split on them, or replace them with a separator, before processing.

How do I extract a UK postcode from an address? Match the outward code (one or two letters, a digit and an optional letter or digit) followed by the inward code (a digit and two letters) with a case-insensitive regular expression, then rejoin the two parts with one space in upper case.

Should I use a library to parse addresses? For mixed international addresses without a country, a statistical parser like libpostal works better; for business data with a known country, simple rules are clearer and easier to review.

Related

• Up one level: Cleaning Excel Data with pandas — tidying Excel data.
• Split a Column into Multiple Columns with pandas — simpler delimiter splits.
• Normalise Postcodes and ZIP Codes with pandas — postcode formats in depth.
• Standardise Phone Numbers in Excel Data with Python — the other contact field.