Python Excel AutomationPractical guides for developers
Fix
Advanced Data Transformation And CleaningDeep dive

Fix Garbled Characters Like "Café" and "’" in Excel Data

Text that shows "Müller", "€100" or "’" was UTF-8 read as Windows-1252 somewhere upstream. Detect the affected cells, repair them with a one-line re-encode or with ftfy, avoid the repairs that damage correct text, recognise what cannot be recovered, and stop it happening again.
Data cleaningFile formats & conversionpandas

A customer list comes back from somewhere with names like Müller GmbH, prices like €100 and apostrophes turned into ’. This is mojibake, and it almost always has the same cause: text that was stored as UTF-8 was, at some step, read as Windows-1252 (or Latin-1). Each accented character is two or three bytes in UTF-8; decoded one byte at a time, those bytes become two or three unrelated characters. Because the damage follows a fixed rule, it can usually be reversed exactly — with care, because the same reversal applied to text that was never damaged can damage it. This page, part of Cleaning Excel Data With Pandas, repairs the damage and shows how to stop it at the source.

How "é" turns into "é" The character é is saved as UTF-8, which encodes it as two bytes, C3 and A9. A later step reads the bytes as Windows-1252, a single-byte encoding, and turns C3 into à and A9 into ©. The text now shows é where é should be. Encoding the text back to Windows-1252 bytes and decoding as UTF-8 reverses it. é the original character UTF-8 bytes C3 A9 saved correctly Read as cp1252 one byte = one character é what the cell now shows

Prerequisites

Bash
pip install pandas openpyxl ftfy

customers.xlsx has seven names. Six were garbled by encoding them as UTF-8 and decoding as Windows-1252 — including one containing curly quotes — and one, Ã is a letter, is correct text that merely contains an Ã.

Step 1 — Find the cells that look garbled

Repair only what is broken. Mojibake from UTF-8 read as Windows-1252 leaves recognisable traces: à or  followed by another Latin-1 character, and the sequence †that starts every garbled curly quote, dash and euro sign:

Python
import re
import pandas as pd

customers = pd.read_excel("customers.xlsx")
MOJIBAKE = re.compile(r"[ÃÂ][\u0080-¿]|â€|€|�")
customers["looks_garbled"] = customers["customer"].str.contains(MOJIBAKE)
print(customers.to_string(index=False))

Six rows are flagged. à is a letter is not: its à is followed by a space, which is not a continuation byte. The pattern also catches the replacement character �, which marks bytes that were lost entirely — Step 4 deals with those.

Step 2 — Reverse the wrong decoding

The repair is the damage run backwards: turn the text back into the bytes it was wrongly decoded from, then decode those bytes as UTF-8. If either step fails, the cell was not simple mojibake and is left alone:

Python
def repair(text):
    try:
        return text.encode("cp1252").decode("utf-8")
    except (UnicodeEncodeError, UnicodeDecodeError):
        return text

customers["repaired"] = customers["customer"].where(~customers["looks_garbled"],
                                                    customers["customer"].map(repair))
print(customers[["customer", "repaired"]].to_string(index=False))

Five of the six flagged names come back exactly: Café Rouge, Müller GmbH, naïve – plan, €100 voucher, São Paulo. The sixth, the quote, does not repair — it contains a �, and encode("cp1252") cannot turn that back into a byte. Applying repair only to flagged rows matters: it is exact on mojibake, but on some correct text it can succeed and produce nonsense, so it should never be run on a whole column blindly.

Garbled names before and after repair Left: Café Rouge, Müller GmbH, €100 voucher and São Paulo as they arrived. Right: the same names after encoding to Windows-1252 bytes and decoding as UTF-8: Café Rouge, Müller GmbH, €100 voucher and São Paulo. A 1 customer 2 Café Rouge 3 Müller GmbH 4 €100 voucher 5 São Paulo A 1 customer 2 Café Rouge 3 Müller GmbH 4 €100 voucher 5 São Paulo as received repaired repair()

Step 3 — Use ftfy for the harder cases, with its options set

The ftfy library knows many more mojibake patterns — text garbled twice, mixtures of encodings, Latin-1 instead of Windows-1252 — and repairs them heuristically. Its defaults also do things you may not want in a data cleaning job, notably replacing curly quotes and apostrophes with straight ones:

Python
import ftfy

broken = customers.loc[customers["looks_garbled"], "customer"]
print([ftfy.fix_text(t) for t in broken])
print([ftfy.fix_text(t, uncurl_quotes=False) for t in broken])

twice = "Café".encode("utf-8").decode("cp1252").encode("utf-8").decode("cp1252")
print(repr(twice), "->", repr(ftfy.fix_text(twice)), "| repair():", repr(repair(twice)))

With uncurl_quotes=False, ftfy leaves typographic quotes as they were meant to be. The double-encoded example shows where ftfy earns its place: text garbled twice (Café) is beyond a single re-encode, and ftfy unwinds it in one call.

ftfy can also be wrong. Run on the correct row, it changes the text:

Python
print(repr(ftfy.fix_text("Ã is a letter")))

ftfy knows that a garbled à often arrives as à followed by a plain space — the character's second byte is a non-breaking space, which many systems turn into an ordinary one — so it treats this perfectly correct text as damaged and "repairs" it. That is why the detection step comes first: apply ftfy to rows that match the mojibake pattern, and keep the original next to the repair so a reviewer can see every change.

Step 4 — Recognise what cannot be recovered

When UTF-8 bytes were decoded as Windows-1252 with errors replaced, any byte Windows-1252 does not define — 0x81, 0x8D, 0x8F, 0x90 and 0x9D — became �. The closing curly quote is encoded with a 0x9D byte, which is why the quote row cannot be restored: the byte is simply not there any more. Neither method brings it back; ftfy repairs the rest of the cell and leaves the �.

Python
unrecoverable = customers["customer"].str.contains("�")
print(customers.loc[unrecoverable, ["id", "customer"]].to_string(index=False))

List these rows separately and fetch them again from the source system, or correct them by hand. Pretending to repair them — guessing a character — creates plausible-looking wrong data.

What each approach does to each kind of cell A grid of cell types against three approaches. Simple mojibake like Café is repaired by the re-encode and by ftfy. Double-encoded text is repaired only by ftfy. Cells containing the replacement character are not recoverable by either. Correct text that merely looks suspicious is left alone by detect-then-repair, but can be changed by ftfy run on everything. Curly quotes are straightened by ftfy defaults unless uncurl_quotes is False. re-encode (flagged rows) ftfy (flagged rows) ftfy on every row Simple: Café Twice: Café Contains � Correct text with à Curly quotes kept with uncurl_quotes=False with uncurl_quotes=False

Scan a whole workbook before cleaning one column

Mojibake rarely affects one column only: if a name was garbled, the address and the notes usually were too. Before repairing, count flagged cells in every text column of every sheet, which also tells you whether the damage is systematic (the whole export) or local (one pasted block):

Python
def mojibake_report(path):
    sheets = pd.read_excel(path, sheet_name=None, dtype=str)
    rows = []
    for name, frame in sheets.items():
        for col in frame.columns:
            hits = frame[col].dropna().str.contains(MOJIBAKE).sum()
            if hits:
                rows.append({"sheet": name, "column": col, "cells": int(hits), "of": int(frame[col].notna().sum())})
    return pd.DataFrame(rows)

print(mojibake_report("customers.xlsx"))

Six of seven cells in one column is systematic damage — the whole export was decoded wrongly and the fix belongs upstream (Step 6). A handful of cells scattered across a large sheet usually means text pasted in from another source, and a targeted repair is enough.

Step 5 — Write the repairs with an audit trail

Write the repaired column next to the original, with a status that says what happened to each row:

Python
def fix_cell(text):
    if not isinstance(text, str) or not MOJIBAKE.search(text):
        return text, "unchanged"
    fixed = ftfy.fix_text(text, uncurl_quotes=False)
    status = "unrecoverable" if "�" in fixed else "repaired"
    return fixed, status

out = customers[["id", "customer"]].copy()
out[["customer_fixed", "status"]] = out["customer"].map(fix_cell).tolist()
out.to_excel("customers_fixed.xlsx", index=False)
print(out.to_string(index=False))

The status column makes the result reviewable: five repaired, one unrecoverable, one unchanged. Once the reviewer is satisfied, replace the original column in the downstream data and keep this workbook as the record of what changed.

Step 6 — Fix it at the source

Downstream repair should be a one-off. Find the step that decodes with the wrong encoding and fix it there. The two usual culprits:

Python
text = "customer\nCafé Rouge\n"
open("export.csv", "w", encoding="utf-8").write(text)

print(pd.read_csv("export.csv", encoding="cp1252")["customer"].tolist())   # the bug
print(pd.read_csv("export.csv", encoding="utf-8")["customer"].tolist())    # the fix

pd.DataFrame({"customer": ["Café Rouge"]}).to_csv("for_excel.csv", index=False, encoding="utf-8-sig")
print(open("for_excel.csv", "rb").read()[:3])

Reading a UTF-8 file with encoding="cp1252" produces exactly the mojibake this page repaired. And a UTF-8 CSV without a byte-order mark, opened by double-clicking in Excel on Windows, is decoded as the local code page — the same damage, done by Excel — which encoding="utf-8-sig" prevents by writing the EF BB BF marker. Convert Excel to CSV with Python covers encodings for CSV output.

Common pitfalls

  • Repairing every row. Detect first; repair only what matches the mojibake pattern.
  • ftfy's default quote handling. Pass uncurl_quotes=False unless straight quotes are what you want.
  • Guessing lost characters. A � is missing data, not a puzzle to solve.
  • Fixing downstream for ever. Correct the encoding at the step that caused it.

Performance notes

The detection regex is vectorised and fast; ftfy is pure Python and processes tens of thousands of short strings per second, which is why it should run only on the flagged subset. For very large tables with little damage, detection first makes the whole job a matter of seconds.

Conclusion

Garbled text like é and ’ is UTF-8 that was decoded as Windows-1252. Find affected cells with a pattern for Ã/ followed by a continuation character and for â€, repair them with text.encode("cp1252").decode("utf-8") or with ftfy.fix_text(..., uncurl_quotes=False) for harder cases, keep the original and a status column, list cells containing � as unrecoverable, and fix the reading or writing step that caused the damage.

Frequently asked questions

What causes text like Café or ’? Text saved as UTF-8 was read back as Windows-1252 (or Latin-1). Each non-ASCII character is two or three bytes in UTF-8, and reading those bytes one at a time as single-byte characters produces the familiar pairs: é becomes é, the curly apostrophe becomes ’.

Can garbled text always be repaired? Only if no bytes were lost. Where the wrong decoding hit a byte that Windows-1252 does not define, the byte was replaced with the replacement character �, and the original information is gone. Those cells need the source data.

Is ftfy safe to run on a whole column? It is careful, but not perfect. By default it also straightens curly quotes, and it can misread legitimate text that happens to look like mojibake. Run it only on cells that match a mojibake pattern, and review the changes.

How do I stop this happening? Find the step that decodes with the wrong encoding — usually reading a UTF-8 CSV without encoding="utf-8", or opening a UTF-8 CSV in Excel without a byte-order mark — and fix it there. Repairing downstream should be a one-off.

More on data cleaning

More on data cleaning →