Standardise Phone Numbers in Excel Data with Python

A phone number column in a customer or supplier spreadsheet is usually a record of how many different people typed into it: 07911 123456, +44 (0)20 7946 0958, (212) 555-0187, 0049 30 901820, sometimes a fax number, sometimes n/a, and occasionally 7911123456.0, a number Excel decided was a number. Any system that sends text messages, dials out or matches customers by phone needs one format, and a person reading the sheet wants a consistent, readable one. Regular expressions get part of the way, but phone numbering rules differ by country — the (0) in a UK number must be dropped after +44, a German 0 trunk prefix likewise, North American numbers have no trunk prefix at all — and the phonenumbers library, a port of Google's libphonenumber, encodes those rules for every country. This page reads the column safely as text, parses each number with a default country per row, writes E.164 for systems and international format for people, flags invalid and suspicious numbers for review, and writes the results back as text. It belongs to Cleaning Excel Data with pandas.

Prerequisites

pip install pandas openpyxl phonenumbers

The sample is a customer list with a country code per customer and a phone column in a dozen styles, including a number stored as a real number in Excel and a placeholder. The country column matters: 030 901820 is a Berlin number for a German customer and meaningless without that context.

Step 1 — Read the column as text

import pandas as pd

raw = pd.read_excel("customers.xlsx")
print(raw["Phone"].map(repr).tolist())

customers = pd.read_excel("customers.xlsx", dtype={"Phone": str}, keep_default_na=False)
print(customers["Phone"].tolist())

Read with default settings, the column is a mix: Harbour Print's phone comes back as the integer 7911123457 — Excel stored it as a number, its leading zero long gone — and Corex's "n/a" has already been turned into NaN. Reading with dtype={"Phone": str} keeps every value as text, and keep_default_na=False stops "n/a" from becoming a missing value before we can see it. The number that Excel already mangled is still mangled — 7911123457 without its 0 — but now it is visible and can be repaired in the next step. Keeping leading zeros in general is covered in Keep Leading Zeros When Reading Excel with pandas.

Step 2 — Parse with a default country per row

import re
import phonenumbers
from phonenumbers import PhoneNumberFormat, PhoneNumberType, NumberParseException

TYPES = {PhoneNumberType.MOBILE: "mobile", PhoneNumberType.FIXED_LINE: "landline",
         PhoneNumberType.FIXED_LINE_OR_MOBILE: "landline or mobile", PhoneNumberType.TOLL_FREE: "toll free"}

def standardise(raw, country):
    text = str(raw).strip()
    if text.lower() in {"", "n/a", "na", "none", "-"}:
        return None, None, None, "missing"
    text = re.sub(r"\.0$", "", text)                       # 7911123457.0 from a float cell
    try:
        number = phonenumbers.parse(text, country)
    except NumberParseException as e:
        return None, None, None, f"unparseable: {e.error_type}"
    if not phonenumbers.is_valid_number(number):
        return None, None, None, "invalid for " + country
    return (phonenumbers.format_number(number, PhoneNumberFormat.E164),
            phonenumbers.format_number(number, PhoneNumberFormat.INTERNATIONAL),
            TYPES.get(phonenumbers.number_type(number), "other"),
            "ok" + (f" (ext {number.extension})" if number.extension else ""))

out = customers.apply(lambda r: pd.Series(standardise(r["Phone"], r["Country"]),
                                          index=["E164", "International", "Type", "Status"]), axis=1)
result = pd.concat([customers, out], axis=1)
print(result[["Customer", "Phone", "E164", "Type", "Status"]].to_string(index=False))

phonenumbers.parse takes the text and a default region — the country to assume when the number has no + country code — so each customer's own country is passed in. It understands the conventions humans use: +44 (0)20… loses the (0), 0049 is read as an international prefix, ext 42 becomes an extension, and dots, spaces and brackets are ignored. is_valid_number then checks the number against that country's real numbering plan — length and allowed prefixes — so 12345 is rejected rather than turned into a plausible-looking +4412345. Harbour Print's number, which Excel stored as the integer 7911123457, needs no special repair: without its trunk 0 it is exactly the UK national significant number, which phonenumbers accepts for the GB region, giving the same +447911123457 the typed version would. That only works because the row's country is known — the same digits under a different default region would be a different, probably invalid, number. The line type distinguishes mobiles from landlines, which matters for anything that sends text messages.

Step 3 — Review list and write back as text

Not every number can be fixed automatically. Separate the ones that need a person, and write the results so Excel cannot damage them again:

review = result[~result["Status"].str.startswith("ok")]
print(review[["Customer", "Phone", "Status"]].to_string(index=False))

from openpyxl import load_workbook
with pd.ExcelWriter("customers_clean.xlsx", engine="openpyxl") as xw:
    result.to_excel(xw, sheet_name="Customers", index=False)
    review.to_excel(xw, sheet_name="Needs review", index=False)
wb = load_workbook("customers_clean.xlsx")
ws = wb["Customers"]
for col in ("C", "D", "E"):
    for cell in ws[col][1:]:
        cell.number_format = "@"
wb.save("customers_clean.xlsx")
check = pd.read_excel("customers_clean.xlsx", dtype=str)["E164"].dropna().tolist()
print("E.164 values all start with '+':", all(v.startswith("+") for v in check))

Two customers go to review: Atlas Supply's 12345 is not a valid UK number and Corex has no number. Every other entry was standardised — including Harbour Print's number from the integer cell, and Delta Office's, with its extension kept separately. Whether a number is a mobile matters as much as whether it is valid when the column feeds a text-message campaign: Zenith's and Delta's US numbers come back as "landline or mobile", because North American numbering does not distinguish the two, so a campaign there needs a separate check with the carrier. The cleaned columns are written as text and given the text format @, so a number like +447911123456 is never reinterpreted as a numeric value by someone editing the sheet, and the round trip confirms every E.164 value kept its +. Store E.164 in systems — it is unambiguous and what SMS and telephony APIs expect — and show the international format to people, who read +44 7911 123456 more easily.

Keeping it clean afterwards

Cleaning a column once is half the job; the same mixture comes back the next time people type into the sheet. Three habits keep it clean. Run the standardisation as part of the import — every time the file is loaded — rather than as a one-off fix, so the system downstream only ever sees E.164. Keep the country column mandatory, because the parsing depends on it; a missing country is worth flagging as loudly as a missing number. And if the workbook is a form people fill in, format the phone column as text before it is distributed, so Excel never converts a typed number into an integer in the first place. The phonenumbers metadata is updated as countries change their numbering plans, so upgrading the package from time to time keeps validation accurate.

Step 4 — Use the standard form to find duplicates

A side effect of standardising is that the same number written two ways becomes the same string, which makes duplicate contacts visible:

dupes = result.dropna(subset=["E164"]).groupby("E164")["Customer"].agg(list)
print(dupes[dupes.map(len) > 1].to_string())

Nordwind GmbH's 0049 30 901820 and Granite Tools' 030 901820 are the same Berlin number, which no comparison of the raw text would have revealed. Whether that means a duplicate customer, a shared switchboard or a data-entry mistake is a question for a person, but the check costs one line once the numbers share a format. The same idea applies to matching the spreadsheet against a CRM or telephony system: compare on E.164, never on the number as typed.

Common pitfalls

• Reading phone columns as numbers. Leading zeros and + signs are lost; use dtype=str.
• One default country for everyone. Pass each row's own country.
• Regex-only cleaning. It cannot know each country's trunk prefixes and lengths.
• Accepting anything that parses. Check is_valid_number as well.
• Writing E.164 back as numbers. Format the cells as text so Excel cannot change them.

Conclusion

Read phone columns as text, parse each number with phonenumbers using the row's own country as the default region, and keep only numbers that pass is_valid_number; digit-only values that lost their trunk zero in Excel still parse correctly when the country is right. Store E.164 for systems and the international format for people, record line type and extensions, send everything that fails to a review sheet, and write the results back as text.

Frequently asked questions

How do I format phone numbers consistently in Python? Parse them with the phonenumbers library, passing a default country, and format with PhoneNumberFormat.E164 or INTERNATIONAL.

Why are leading zeros missing from phone numbers read from Excel? The cells were stored or read as numbers; read the column with dtype=str, and repair numbers Excel already converted by restoring the trunk zero.

How do I check whether a phone number is valid? Use phonenumbers.is_valid_number on the parsed number; it checks the number against the country's numbering plan.

How can I tell mobile numbers from landlines? phonenumbers.number_type returns MOBILE, FIXED_LINE or another type for a parsed number.

Related

• Up one level: Cleaning Excel Data with pandas — tidying Excel data.
• Validate Email and Phone Formats in Excel Data with Python — checking formats without converting.
• Standardise Country Names and Codes with Python — cleaning the country column first.
• Format ZIP Codes and IDs with Leading Zeros — keeping codes intact in Excel.