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

Remove Non-Printable Characters from Excel Data with Python

Find and remove the characters you cannot see in Excel text — carriage returns, tabs, bell and other control codes, soft hyphens, zero-width marks and leftover _x000D_ escapes — using Unicode categories, without damaging line breaks or emoji you want to keep.
Data cleaningpandas

Two product codes look identical in Excel, but a lookup says they are different. A name exports to CSV and breaks the file across two lines. A report shows "Line one_x000D_" where a line break should be. All three are caused by characters you cannot see: control codes, format characters and escape sequences. This guide, part of Cleaning Excel Data with pandas, shows how to find them by Unicode category, decode the escapes openpyxl leaves behind, and remove what should go while keeping the line breaks and emoji you want. Spaces — ordinary, non-breaking and repeated — are covered separately in Strip Whitespace and Normalise Text Columns with pandas.

Invisible characters in Excel text and what to do with them Carriage returns and line feeds are control characters to normalise, not delete. Tabs and bell codes are control characters to remove or replace with a space. The soft hyphen, zero-width space and byte-order mark are format characters to remove. The zero-width joiner inside emoji is a format character to keep. The replacement character U+FFFD is a visible symbol that signals lost data and should be reported. Category Action \r \n inside a cell Cc control normalise \t tab, \x07 bell Cc control remove soft hyphen, zero-width space, BOM Cf format remove zero-width joiner in emoji Cf format keep _x000D_ escape text plain letters decode U+FFFD replacement So symbol report

Prerequisites

Bash
pip install pandas openpyxl python-calamine xlsxwriter

A sample workbook written with xlsxwriter, which stores control characters the same way Excel does:

Python
import re
import unicodedata

import pandas as pd

raw = pd.DataFrame({"sku": ["AB-100", "AB\u200b-100", "\ufeffAB-200", "AB-3\xad00"],
                    "note": ["Line one\r\nLine two", "tab\there", "bell\x07!", "caf\ufffd 👩\u200d💻"]})
raw.to_excel("invisible.xlsx", index=False, engine="xlsxwriter")
df = pd.read_excel("invisible.xlsx")
print(df["sku"].tolist())
print(df["sku"].nunique())        # 4 — although they look like 3 codes

Step 1 — Audit before you delete

Count the characters by Unicode category, per column, so you know what you are dealing with and can explain the change later:

Python
SUSPECT = {"Cc", "Cf", "Co", "Cn"}

def audit(frame):
    rows = []
    for col in frame.select_dtypes(include=["object", "string"]).columns:
        counts = {}
        for value in frame[col].dropna().astype(str):
            for ch in value:
                cat = unicodedata.category(ch)
                if cat in SUSPECT or ch == "\ufffd":
                    name = unicodedata.name(ch, f"U+{ord(ch):04X}")
                    counts[name] = counts.get(name, 0) + 1
        rows += [{"column": col, "character": k, "count": v} for k, v in counts.items()]
    return pd.DataFrame(rows)

print(audit(df))

The report names each character where Unicode gives it a name — ZERO WIDTH SPACE, SOFT HYPHEN, ZERO WIDTH NO-BREAK SPACE (the byte-order mark), ZERO WIDTH JOINER, REPLACEMENT CHARACTER — rather than showing invisible output; control codes have no names, so the line feed and tab appear as U+000A and U+0009. Category Cc holds control codes, Cf format characters, Co private-use characters from custom fonts, and Cn unassigned code points. The note column also contains the texts _x000D_ and _x0007_ (the carriage return and the bell), which the audit does not flag at all, because those are ordinary letters.

Step 2 — Decode the x000D escapes

Excel's file format cannot store most control characters directly, so it writes them as _xHHHH_ escape sequences — a carriage return becomes _x000D_. The calamine engine decodes these; openpyxl, the default engine for read_excel, leaves them in the text:

Python
print(repr(pd.read_excel("invisible.xlsx")["note"][0]))
# 'Line one_x000D_\nLine two'
print(repr(pd.read_excel("invisible.xlsx", engine="calamine")["note"][0]))
# 'Line one\r\nLine two'

If you read with openpyxl, decode the escapes yourself before any other cleaning:

Python
ESCAPE = re.compile(r"_x([0-9A-Fa-f]{4})_")

def decode_escapes(text):
    return ESCAPE.sub(lambda m: chr(int(m.group(1), 16)), text) if isinstance(text, str) else text

df["note"] = df["note"].map(decode_escapes)
print(repr(df["note"][0]))        # 'Line one\r\nLine two'

There is one ambiguity: a cell whose real text is _x000D_ — rare outside of documentation — is indistinguishable from an escaped carriage return once openpyxl has read it. When that matters, read with engine="calamine", which keeps the two apart.

Cleaning invisible characters in the right order Audit the text columns by Unicode category, decode _x000D_ escapes, normalise line breaks to a single line feed, remove control and format characters except an allow list, then flag replacement characters and compare the audit before and after. Audit count by category Decode escapes _x000D_ → \r Normalise breaks \r\n → \n Remove Cc and Cf keep allow list Flag U+FFFD lost upstream

Step 3 — Decide what a line break means

A line break typed with Alt+Enter is intentional in an address or a comment, and wrong in a product code. Normalise every variant to \n first — Excel files contain \r\n, \r and \n depending on where the text came from — then decide per column:

Python
def normalise_breaks(text):
    return re.sub(r"\r\n?", "\n", text) if isinstance(text, str) else text

df["note"] = df["note"].map(normalise_breaks)
single_line = df["note"].str.replace(r"\s*\n\s*", " ", regex=True)
print(repr(df["note"][0]), "|", single_line[0])
# 'Line one\nLine two' | Line one Line two

Keep \n in columns that will be shown in a wrapped Excel cell; replace it with a space in columns bound for CSV files, database keys or single-line labels.

Step 4 — Remove control and format characters

With escapes decoded and breaks normalised, remove every remaining control or format character, except for a short allow list of the ones your data genuinely needs:

Python
KEEP = {"\n", "\u200d", "\u200e", "\u200f"}      # line feed, zero-width joiner, direction marks

def strip_invisible(text, keep=KEEP, tab=" "):
    if not isinstance(text, str):
        return text
    out = []
    for ch in text:
        if ch == "\t":
            out.append(tab)
        elif ch in keep or unicodedata.category(ch) not in {"Cc", "Cf", "Co", "Cn"}:
            out.append(ch)
    return "".join(out)

text_cols = df.select_dtypes(include=["object", "string"]).columns
df[text_cols] = df[text_cols].apply(lambda s: s.map(strip_invisible))
print(df["sku"].tolist())        # ['AB-100', 'AB-100', 'AB-200', 'AB-300']
print(df["sku"].nunique())       # 3
print(df["note"].tolist())

The zero-width space, byte-order mark and soft hyphen are gone, so the four codes collapse to the three real ones. Tabs become spaces rather than vanishing, so "tab↹here" does not turn into "tabhere". The zero-width joiner is kept because it binds the woman and laptop emoji into a single "technologist" symbol; removing it splits them in two. Direction marks are kept for text in right-to-left scripts. Adjust the allow list to your data — for purely numeric codes, keep nothing.

Step 5 — Report replacement characters, do not delete them

U+FFFD (�) is not invisible; it is what a program writes when it could not decode a byte. The original character is already lost — "café" became "caf�" somewhere upstream, usually in a CSV read with the wrong encoding before it reached Excel. Deleting it produces "caf", which is wrong in a different way. List the affected rows instead so the source can be fixed:

Python
damaged = df[df["note"].str.contains("\ufffd", na=False)]
print(damaged.index.tolist())    # [3]

Encoding problems at the CSV stage are covered in Convert CSV Files to Excel with Python.

Step 6 — Confirm the result

Run the audit again and compare. Anything left should be on your allow list — here the kept line feed and joiner, plus the reported replacement character:

Python
after = audit(df)
print(after[["column", "character", "count"]].values.tolist())
# [['note', 'U+000A', 1], ['note', 'REPLACEMENT CHARACTER', 1], ['note', 'ZERO WIDTH JOINER', 1]]
df.to_excel("clean.xlsx", index=False)

Saving the before and after audits with the output gives you a record of exactly what changed, which is useful when someone asks why a code in the cleaned file differs from the source.

Four product codes become three Before cleaning, AB-100 appears twice, once with a zero-width space, AB-200 carries a byte-order mark and AB-300 contains a soft hyphen, so pandas counts four distinct codes. After removing format characters the codes are AB-100, AB-100, AB-200 and AB-300, and pandas counts three. A 1 AB-100 2 AB·-100 (ZWSP) 3 ·AB-200 (BOM) 4 AB-3·00 (SHY) A 1 AB-100 2 AB-100 3 AB-200 4 AB-300 before after strip_invisible

Step 7 — Clean every sheet in one pass

Invisible characters rarely stay in one column. Wrap the steps in a single function and apply it to every text column of every sheet, so the whole workbook is treated consistently:

Python
def clean_text(value):
    return strip_invisible(normalise_breaks(decode_escapes(value)))

sheets = pd.read_excel("invisible.xlsx", sheet_name=None)
with pd.ExcelWriter("invisible_clean.xlsx") as writer:
    for name, frame in sheets.items():
        cols = frame.select_dtypes(include=["object", "string"]).columns
        frame[cols] = frame[cols].apply(lambda s: s.map(clean_text))
        frame.to_excel(writer, sheet_name=name, index=False)
print(pd.read_excel("invisible_clean.xlsx")["sku"].nunique())   # 3

The order inside clean_text is the order of the steps above, and it matters: escapes must become characters before line breaks can be normalised, and breaks must be normalised before the stripping step decides which characters to keep. Selecting only text columns leaves numbers and dates untouched, so the cleaning cannot change a figure.

Common pitfalls

  • Relying on Excel's CLEAN. It removes ASCII codes 0–31 only; the soft hyphen, zero-width space and byte-order mark survive it.
  • Deleting all Cf characters. That breaks emoji sequences, flags and right-to-left text. Use an allow list.
  • Cleaning before decoding. If you strip characters before decoding _x000D_, the escape text survives as letters and appears in the report.
  • Writing control characters back. openpyxl refuses most of them with IllegalCharacterError; clean first, as described in Fix IllegalCharacterError When Writing Excel with openpyxl.

Conclusion

Invisible characters make identical-looking values unequal and break exports. Audit text columns by Unicode category first, decode _xHHHH_ escapes (or read with calamine), normalise line breaks and decide per column whether to keep them, then remove control and format characters with an allow list for joiners and direction marks. Report replacement characters instead of deleting them, and re-run the audit to confirm what is left is intentional.

Frequently asked questions

Why do I see x000D in text read from Excel? Excel stores a carriage return inside a cell as the escape sequence x000D. The calamine engine decodes it back to a character, but openpyxl — the default engine for pandas.read_excel — leaves the escape in the text. Decode it with a regular expression or read with engine="calamine".

What counts as a non-printable character? In Unicode terms, control characters (category Cc, such as tab, carriage return and bell) and format characters (category Cf, such as the soft hyphen, zero-width space and byte-order mark). They take no visible space but still make two strings unequal.

Will removing format characters break emoji? It can. Some emoji are joined with a zero-width joiner (U+200D), and some scripts need direction marks. Keep an allow list of the format characters your data legitimately uses.

Is Excel's CLEAN function the same as this? CLEAN removes only the first 32 ASCII control codes. It leaves soft hyphens, zero-width characters, the byte-order mark and other Unicode format characters in place, which is why Python-side cleaning based on Unicode categories is more thorough.

More on data cleaning

More on data cleaning →