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

Validate Email and Phone Formats in Excel Data with Python

Check the email addresses and phone numbers in a contact sheet before importing it — a quick regex screen, email-validator for real address syntax, phonenumbers for national and international formats, normalised values for deduplication, and a report of invalid cells with their spreadsheet row numbers.
Validation & data qualityData cleaningpandas

A contact list maintained in Excel is only as good as the data people typed into it: "cai@example" with no domain ending, "dee@@example.com", a phone number stored as 7911123456 because Excel dropped the leading zero, and the same office number written three different ways. Import that into a CRM or mailing tool and the bad rows bounce, fail silently or create duplicates. This guide, part of Validating Excel Data with Python, checks both columns properly, normalises the valid values and produces a report of the invalid cells with their row numbers in the sheet.

A contact sheet with typical format problems Ann's email and phone are fine. Bob's email has upper-case letters and a trailing space and his phone uses the international format. Cai's email has no dot in the domain, Dee's email has two at signs, and Eve's phone was stored as a number without its leading zero. A B C 1 name email phone 2 Ann ann@example.com 020 7946 0958 3 Bob Bob.Ray@Example.COM␠ +44 20 7946 0958 4 Cai cai@example 07520 123456 5 Dee dee@@example.com 12345 6 Eve eve@example.co.uk 7911123456 pink cells are invalid; gold cells are valid but need normalising

Prerequisites

Bash
pip install pandas openpyxl email-validator phonenumbers

The sample sheet, with Eve's phone stored as a number the way Excel stores digits typed without spaces:

Python
import pandas as pd
from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.append(["name", "email", "phone"])
for row in [["Ann", "ann@example.com", "020 7946 0958"],
            ["Bob", "Bob.Ray@Example.COM ", "+44 20 7946 0958"],
            ["Cai", "cai@example", "07520 123456"],
            ["Dee", "dee@@example.com", "12345"],
            ["Eve", "eve@example.co.uk", 7911123456]]:
    ws.append(row)
wb.save("contacts.xlsx")

df = pd.read_excel("contacts.xlsx", dtype={"phone": str, "email": str})
df.insert(0, "sheet_row", df.index + 2)
print(df["phone"].tolist())
# ['020 7946 0958', '+44 20 7946 0958', '07520 123456', '12345', '7911123456']

Reading phone as text is the most important line on this page. Without it, a column where every number is stored as digits comes back as integers — or as floats like 7911123456.0 if any cell is blank — and the phone library then sees a different number. The sheet_row column records the spreadsheet row for each record, for the report at the end.

Step 1 — A quick regex screen

A regular expression catches the gross errors cheaply and needs no extra library. Keep it deliberately loose; its job is to flag obvious junk, not to judge borderline addresses:

Python
SCREEN = r"^[^@\s]+@[^@\s]+\.[^@\s]+$"
email = df["email"].str.strip()
print(email.str.match(SCREEN).tolist())    # [True, True, False, False, True]

The pattern requires one @, no spaces, and a dot somewhere after the @. That rejects Cai and Dee. It would also accept plenty of invalid addresses — a@b..c, for instance — which is why the next step uses a real validator.

Step 2 — Validate and normalise emails

email-validator implements the address syntax rules, including internationalised domains, and returns a normalised form. Turn off the DNS lookup for bulk checks; it is slow and needs network access:

Python
from email_validator import EmailNotValidError, validate_email

def check_email(value):
    if pd.isna(value) or not str(value).strip():
        return None, "missing"
    try:
        result = validate_email(str(value).strip(), check_deliverability=False)
        return result.normalized, None
    except EmailNotValidError as exc:
        return None, str(exc)

checked = df["email"].map(check_email)
df["email_norm"] = checked.str[0]
df["email_error"] = checked.str[1]
print(df[["email_norm", "email_error"]].values.tolist())

Bob's address comes back as Bob.Ray@example.com: the domain is lower-cased, while the part before the @ keeps its case, because in principle a mail server may treat it as case-sensitive. The errors are readable sentences — "The part after the @-sign is not valid. It should have a period." — which can go straight into a report for the person who maintains the sheet. Valid syntax does not prove the mailbox exists; only sending a message does.

From raw contact columns to a validation report Read email and phone as text, screen emails with a loose regex, validate and normalise each address with email-validator and each phone number with phonenumbers using a default region, detect duplicates on the normalised values, then write a report of invalid cells with their sheet rows. Read as text dtype=str Validate emails email-validator Parse phones phonenumbers, region GB Normalise domain case, E.164 Report row, column, problem

Step 3 — Parse phone numbers with a default region

Phone numbers need a library: the rules for valid lengths and prefixes differ by country and change over time. phonenumbers, a port of Google's libphonenumber, parses national and international formats. A default region tells it how to read numbers written without a country code:

Python
import phonenumbers as pn

def check_phone(value, region="GB"):
    if pd.isna(value) or not str(value).strip():
        return None, "missing"
    try:
        number = pn.parse(str(value), region)
    except pn.NumberParseException as exc:
        return None, str(exc)
    if not pn.is_valid_number(number):
        return None, "not a valid number for its region"
    return pn.format_number(number, pn.PhoneNumberFormat.E164), None

checked = df["phone"].map(check_phone)
df["phone_norm"] = checked.str[0]
df["phone_error"] = checked.str[1]
print(df[["name", "phone_norm", "phone_error"]].values.tolist())

Ann's "020 7946 0958" and Bob's "+44 20 7946 0958" both become +442079460958. Eve's 7911123456 — the number Excel stripped of its leading zero — is parsed as a UK mobile and restored to +447911123456. Dee's "12345" is rejected: it is not a valid UK number. If a contact list spans several countries, store the country in its own column and pass it as the region per row. pn.number_type(number) also tells you whether a number is a mobile, which matters when the list is used for text messages.

Step 4 — Find duplicates after normalising

Normalised values expose duplicates that formatting hid. Ann and Bob share an office number written two different ways:

Python
dupe_phones = df[df["phone_norm"].notna() & df["phone_norm"].duplicated(keep=False)]
print(dupe_phones[["sheet_row", "name", "phone"]].values.tolist())
# [[2, 'Ann', '020 7946 0958'], [3, 'Bob', '+44 20 7946 0958']]

A shared switchboard number is legitimate; a shared mobile or email usually means the same person entered twice. Report duplicates for review rather than dropping them. Run the same check on email_norm — the domain is already lower-cased there, so "Ann@EXAMPLE.com" and "Ann@example.com" match. Duplicate detection more generally is covered in Find Duplicate Rows in Excel with Python.

Step 5 — Report the invalid cells

Collect every problem as one row — sheet row, column, the value as typed and the reason — which is the most useful shape for whoever has to fix the source:

Python
problems = pd.concat([
    df.loc[df["email_error"].notna(), ["sheet_row", "email", "email_error"]]
      .set_axis(["sheet_row", "value", "problem"], axis=1).assign(column="email"),
    df.loc[df["phone_error"].notna(), ["sheet_row", "phone", "phone_error"]]
      .set_axis(["sheet_row", "value", "problem"], axis=1).assign(column="phone"),
]).sort_values("sheet_row")[["sheet_row", "column", "value", "problem"]]
print(problems.values.tolist())
problems.to_excel("contact_problems.xlsx", index=False)

Three problems, each pointing at a cell someone can open and correct: rows 4 and 5 for the two bad emails and row 5 for Dee's phone. Colouring those cells in the original workbook makes the fix even faster; see Highlight Invalid Cells in Excel with Python.

The outcome for each contact Ann is valid on both columns. Bob is valid once his email domain is lower-cased and his phone normalised. Cai has an invalid email. Dee has an invalid email and an invalid phone. Eve's email is valid and her phone is valid once the leading zero Excel removed is restored. email phone Ann Bob normalised normalised Cai Dee Eve restored

Step 6 — Write the normalised values back

Once the problems are fixed at the source, the normalised columns are what should travel on. Write them next to the originals so the change is visible, and use a text format for the phone column so Excel never turns +447911123456 into a number again:

Python
from openpyxl import load_workbook

out = df[["sheet_row", "name", "email", "email_norm", "phone", "phone_norm"]]
out.to_excel("contacts_checked.xlsx", index=False)
wb = load_workbook("contacts_checked.xlsx")
ws = wb.active
for (cell,) in ws.iter_rows(min_row=2, min_col=6, max_col=6):
    cell.number_format = "@"
wb.save("contacts_checked.xlsx")
print(pd.read_excel("contacts_checked.xlsx", dtype=str)["phone_norm"].tolist()[:2])

The @ number format marks the cells as text, so a person who later edits a number in Excel keeps the plus sign and leading digits. Keeping the original columns alongside the normalised ones lets reviewers check that normalisation did what they expected before the old values are dropped.

Common pitfalls

  • Reading phone numbers as numbers. Leading zeros and plus signs disappear, and blanks turn the column into floats. Always use dtype=str.
  • Rejecting valid addresses with a strict regex. Plus signs, long domain endings and non-ASCII characters are all legal. Use the regex only as a screen.
  • Extensions and notes in phone cells. phonenumbers understands "020 7946 0958 ext. 12" and keeps the extension in number.extension, but E.164 has no place for it. Store extensions in their own column, and treat free text such as "call after 5pm" as a problem to report.
  • Validating once. Formats rot as people edit the sheet. Run the checks on every import, not just the first.

Conclusion

Read contact columns as text, screen emails with a loose regex, then validate and normalise them with email-validator and phone numbers with phonenumbers using a default region. Store emails with lower-cased domains and phones in E.164 so that duplicates become visible, and report every invalid cell with its sheet row, column and reason so the source can be fixed.

Frequently asked questions

Is a regular expression enough to validate email addresses? It is enough for a quick screen that catches blanks, missing @ signs and spaces. For real validation, email-validator checks the full syntax, including domains and internationalised addresses, and returns a normalised form. Neither proves the mailbox exists.

Why do phone numbers from Excel lose their leading zero? When a phone number is typed without spaces, Excel stores it as a number, and 07911123456 becomes 7911123456. Read the column as text with dtype=str and parse it with a default region, which restores the national format correctly.

What is E.164 format? The international format with a plus sign, country code and national number without spaces or punctuation, such as +447911123456. It is the best form to store and compare phone numbers, because every way of writing the same number normalises to one string.

How do I find duplicate contacts with different formatting? Normalise first — lower-case email domains and E.164 phone numbers — and then look for duplicates on the normalised columns. "020 7946 0958" and "+44 20 7946 0958" are the same number once normalised.

More on validation & data quality

More on validation & data quality →