Remove Accents and Normalise Unicode in Excel Text

Two customer lists both contain "Café Señor", yet a merge in pandas finds no match. drop_duplicates leaves "Müller GmbH" in twice. A filter for "Zürich" misses half the rows. Nothing is visibly different — the text looks identical — but the underlying characters are not. Unicode allows the same visible letter to be stored in more than one way: an "é" can be one character or an "e" followed by a combining accent; a space can be a normal space, a non-breaking space or a zero-width space that is not visible at all; a digit can be a normal "1" or a full-width "１" pasted from an East Asian system. Excel displays them all the same. This page shows how to detect these differences, normalise text with Python's unicodedata, build accent-free keys for matching, and keep the original spelling for display. It belongs to Cleaning Excel Data with pandas.

Prerequisites

pip install pandas openpyxl

customers.xlsx has two sheets: a CRM list of six customers and a sheet of web orders for the same customers, typed or pasted differently. To the eye, several names are identical; underneath, one web name uses a decomposed accent, one a non-breaking space and one a hidden zero-width space, two were typed in capitals without accents, and one CRM name contains the typographic ligature "fi" from a word processor. Everything needed is in Python's standard library unicodedata module.

Step 1 — See that "identical" text does not match

Try the obvious merge and then look at the actual characters:

import unicodedata
import pandas as pd

crm = pd.read_excel("customers.xlsx", sheet_name="CRM")
web = pd.read_excel("customers.xlsx", sheet_name="Web orders")
plain = web.merge(crm, on="Customer", how="left")
print(plain[["Customer", "Account"]].to_string(index=False))

def reveal(text):
    return " ".join(f"U+{ord(ch):04X}" if not ch.isascii() or not ch.isprintable() else ch for ch in text)

for name in web["Customer"][:3]:
    print(repr(name), "→", reveal(name))

The merge finds no account for any of the web orders. Revealing the code points shows why: the web "Café" is e followed by U+0301 (a combining acute accent) where the CRM has the single character U+00E9; "Müller GmbH" has U+00A0, a non-breaking space, between the words; and "Zürich Optik" hides a U+200B zero-width space before the normal space. A helper like reveal is worth keeping around: when two strings look equal and are not, printing their code points answers the question in seconds.

Step 2 — Normalise to one form with NFC or NFKC

unicodedata.normalize converts text to a standard normal form. NFC composes letters and accents into single characters wherever possible; NFKC additionally replaces compatibility characters — ligatures, full-width forms, superscripts, non-breaking spaces — with their plain equivalents:

samples = {"decomposed é": "Café", "nbsp": "Müller GmbH", "ligature": "Office fitters", "full-width": "ＳＫＵ１２"}
for label, s in samples.items():
    print(f"{label:13} NFC={unicodedata.normalize('NFC', s)!r:18} NFKC={unicodedata.normalize('NFKC', s)!r}")

NFC fixes the decomposed accent — Cafe + combining accent becomes Café with a single é — but leaves the other three alone, because a ligature or a non-breaking space is a different character, not just a different encoding. NFKC fixes all four: the ligature fi becomes fi, the non-breaking space a normal space and the full-width ＳＫＵ１２ becomes SKU12. For data cleaning, NFKC is usually the right choice; NFC is the safe choice when text will be displayed or printed and typographic characters should be kept. Neither removes zero-width characters, which is the next step.

Step 3 — A cleaning function for names and keys

Combine NFKC with removal of invisible format characters and collapsing of whitespace. That gives a cleaned display value; for matching, go further — case-fold and strip accents:

import re

INVISIBLE = re.compile(r"[​-‏⁠]")      # zero-width spaces, joiners, marks, BOM

def clean_text(text):
    text = unicodedata.normalize("NFKC", str(text))
    text = INVISIBLE.sub("", text)
    return re.sub(r"\s+", " ", text).strip()

def match_key(text):
    decomposed = unicodedata.normalize("NFKD", clean_text(text))
    no_accents = "".join(ch for ch in decomposed if not unicodedata.combining(ch))
    return no_accents.casefold()

for raw in ["Zürich​ Optik", "STRASSE & SÖHNE", "Straße & Söhne", "Ångström AB", "ANGSTROM AB"]:
    print(f"{raw!r:24} clean={clean_text(raw)!r:20} key={match_key(raw)!r}")

clean_text produces a tidy, still-accented value suitable for display: the zero-width space is gone and spacing is normal. match_key decomposes characters with NFKD so each accent becomes a separate combining mark, drops the marks with unicodedata.combining, and case-folds. casefold rather than lower matters for German: "Straße".casefold() is "strasse", so "Straße & Söhne" and "STRASSE & SÖHNE" produce the same key, which lower() would not achieve. "Ångström AB" and "ANGSTROM AB" match for the same reason. The key is for comparing, never for showing — "Muller" is not how Mr Müller spells his name.

Step 4 — Match on keys, keep the originals

Merge on the keys, while keeping each side's original text for display:

crm["key"] = crm["Customer"].map(match_key)
web["key"] = web["Customer"].map(match_key)
matched = web.merge(crm[["key", "Account", "Customer"]], on="key", how="left", suffixes=("", " (CRM)"))
print(matched[["Customer", "Customer (CRM)", "Account", "Order total"]].to_string(index=False))
print("unmatched:", matched["Account"].isna().sum())

All six web orders now find their account. The output shows both spellings side by side — the web's shouted "STRASSE & SÖHNE" next to the CRM's "Straße & Söhne" — and the report can use the CRM's version for display. Matching on accent-free keys can occasionally join names that are genuinely different only by an accent; check the keys for duplicates on the reference side before merging, as in Validate a Merge to Avoid Duplicate Rows.

Step 5 — Smart quotes, dashes and other look-alikes

Text pasted from word processors also brings typographic punctuation that NFKC leaves alone: curly quotes, en and em dashes, ellipses. If a downstream system expects plain ASCII, or you match on these characters, translate them explicitly:

PUNCT = str.maketrans({"‘": "'", "’": "'", "“": '"', "”": '"',
                       "–": "-", "—": "-", "…": "...", "´": "'"})
pasted = "O’Brien – “Premium” range…"
print(pasted, "→", pasted.translate(PUNCT))

def find_odd(series):
    odd = series.map(lambda s: sorted({f"U+{ord(c):04X} {unicodedata.name(c, '?')}" for c in str(s)
                                      if not c.isascii() and unicodedata.category(c)[0] in "ZPC"}))
    return odd[odd.str.len() > 0]

print(find_odd(web["Customer"]).to_dict())

str.translate with a mapping table replaces each typographic character with its plain equivalent in one pass: O'Brien keeps its apostrophe, but now a straight one, so it matches the same name typed on a keyboard. find_odd lists every non-ASCII space, punctuation or control character in a column, with its official Unicode name — a quick audit of what a column actually contains, run before deciding what to normalise. Mojibake — text that was decoded with the wrong encoding, such as "Café" — is a different problem with a different fix, covered in Fix Garbled Characters in Excel Data.

Where to normalise

Normalise as early as possible — right after reading each source — so every later step, from filters to joins to pivot tables, sees one form. Store the cleaned display value (NFKC, no invisible characters) in place of the raw text if the raw form has no value of its own, or alongside it if the original must be preserved for audit; compute match keys on the fly or in a separate column, never in place of the name. When writing results back to Excel, write the cleaned values: a workbook full of non-breaking spaces and zero-width characters will cause the same lookup failures for whoever uses it next, this time in VLOOKUP and COUNTIF formulas that are even harder to debug than pandas merges. And when a new source joins the pipeline, run the find_odd audit on its text columns first — it takes a second and tells you exactly which of these problems that source brings.

Common pitfalls

• Assuming text that looks equal is equal. Print code points when comparisons fail.
• NFC when the problem is a non-breaking space or ligature. Use NFKC for cleaning.
• Forgetting zero-width characters. No normal form removes them; strip them explicitly.
• lower() for matching. Use casefold(), which handles ß and other special cases.
• Displaying the accent-free key. Keep the original spelling for anything people read.

Conclusion

Text from different sources can look identical and still not match, because Unicode stores accents, spaces, ligatures and full-width characters in more than one way and allows invisible characters. Normalise with unicodedata.normalize("NFKC", ...), remove zero-width characters and collapse whitespace for a clean display value, translate typographic punctuation when needed, and build a separate match key with NFKD, accent removal and casefold(). Merge and de-duplicate on the keys, and keep the original spellings for display.

Frequently asked questions

Why don't two identical-looking strings match in pandas? They contain different Unicode characters, such as a decomposed accent, a non-breaking space or an invisible zero-width space. Normalise both with unicodedata and remove invisible characters before comparing.

What is the difference between NFC and NFKC? NFC combines letters and accents into single characters. NFKC also replaces compatibility characters, such as ligatures, full-width letters and non-breaking spaces, with their plain equivalents.

How do I remove accents from text in Python? Normalise with NFKD so accents become separate combining marks, then keep only characters for which unicodedata.combining returns 0.

Should I store text without accents? Use accent-free text only as a key for matching and de-duplication. Keep and display the original spelling.

Related

• Up one level: Cleaning Excel Data with pandas — tidy data before analysis.
• Remove Non-Printable Characters from Excel Data — control characters and other invisibles.
• Merge on Keys That Differ in Case and Spacing with pandas — simpler key normalisation.
• Fix the Capitalisation of Names with Exceptions in pandas — the display side of name cleaning.