Your spreadsheet lookup fails because of three invisible characters

Claire Nagase — Tue, 20 Oct 2026 15:29:44 +0000

A reader sent me a spreadsheet last week with a lookup that returned #N/A for every row. The values were obviously there. You could see Widget in column A and Widget in the lookup table. Identical, as far as the screen was concerned.

They were not identical. One of them was Widget followed by a non-breaking space, a character that renders as nothing and survives every copy-paste, every save, and every "just retype it" attempt.

This is the most common spreadsheet bug I know of, and it is invisible by construction. Here are the four characters responsible, how to find them in ten seconds, and a normaliser you can paste into your own workflow.

Why the values look identical

A spreadsheet shows glyphs. Your comparison runs on code points. Between those two things sits a whole category of characters that occupy no visible width:

U+FEFF, the byte-order mark. Excel writes it at the start of every "CSV UTF-8" export. If it lands in the first header cell, your column is not called Name, it is called \ufeffName. Every lookup against Name fails, and the header looks completely normal.
U+00A0, the non-breaking space. Web pages and HTML-to-Excel exports are full of them. It looks like a space, sorts like a space, and is not " ". strip() in most languages does not remove it by default.
U+200B through U+200D, zero-width characters. Copy text out of a PDF, a chat window or a CMS and you can pick these up. They are not spaces at all; they are formatting marks with no width.
Duplicate header rows. Concatenate twelve monthly exports and the header row appears in the middle of your data, splitting types and breaking lookups halfway down the file.

Finding them in ten seconds

Stop staring at the cells. Ask the machine what is actually in them:

import unicodedata

def show_invisible(value):
    return "".join(
        f"\\u{ord(ch):04x}"
        if unicodedata.category(ch) in ("Cc", "Cf", "Zs") and ch != " "
        else ch
        for ch in value
    )

print(show_invisible("\ufeffName\u00a0"))   # \ufeffName\u00a0
print(len("\ufeffName"), len("Name"))       # 5 4

The len() line is the one that ends arguments. If the header is five characters and you typed four, you have found your bug. In Excel the same check is =LEN(A1), which is the fastest diagnostic available and works on any machine without installing anything.

The normaliser

Once you know what you are dealing with, cleaning is a few lines. This handles the BOM, the non-breaking space, zero-width characters, and stray padding:

import re

def normalise(value):
    value = value.replace("\ufeff", "")                  # byte-order mark
    value = value.replace("\u00a0", " ")                 # non-breaking space
    value = re.sub(r"[\u200b-\u200d\u2060]", "", value)  # zero-width marks
    return value.strip()

Run it over the header row and every cell you are about to match on. Order matters: replace the non-breaking space with a normal space before stripping, otherwise strip() leaves it in place and you have fixed nothing.

Fixing the file, not the symptom

Doing this by hand is fine for one column and hopeless for twenty thousand rows across forty files. The durable fix is to clean on the way in, once, and never think about it again:

import csv, io

with open("export.csv", "rb") as fh:
    text = fh.read().decode("utf-8-sig")   # "utf-8-sig" eats the BOM for you

rows = list(csv.reader(io.StringIO(text)))
header = [normalise(c).lower().replace(" ", "_") for c in rows[0]]
data = [[normalise(c) for c in row] for row in rows[1:] if any(c.strip() for c in row)]

Three details in there are worth more than they look:

utf-8-sig, not utf-8. The -sig variant strips the byte-order mark as it decodes. Without it you are back to a phantom character in column one.
any(c.strip() for c in row) drops fully empty rows, which otherwise become rows of empty strings that break int() and float() later.
Lowercasing and underscoring headers means " Name " and "name" and "NAME" all become name. Header drift is the second most common cause of broken lookups after invisible characters.

For the mojibake flavour — caf\u00c3\u00a9 instead of café — the cause is different: text was decoded with the wrong encoding, then encoded back. Reversing it is a round trip: encode with the wrong codec, decode with the right one. It works, but only on text that has not been through two wrong conversions, which is why backups matter more than cleverness there.

The habit that prevents all of it

Treat every export as hostile until proven otherwise. Decode with utf-8-sig, normalise every string, and assert on your header names before you do any matching. It takes four lines and it removes an entire class of bug from your life.

The spreadsheets I get are rarely malicious, but they are always from somewhere else: a CRM, a payment processor, a client on Windows 7. Their encoding choices are not your fault, but they are your problem the moment the lookup fails.

The normaliser above is free regardless — go fix your lookups. If you would rather not maintain the scripts yourself, I put the two most common chores from this article into a free sampler: renaming a folder into date order, and merging a pile of spreadsheets into one table, with the setup chapter for people who have never run Python: clairenagase.gumroad.com/l/wqqsal. The full guide and the other six chores live at clairenagase.gumroad.com/l/lnlafw.

Seven CLI tools, zero dependencies: what the standard library actually costs

Claire Nagase — Tue, 20 Oct 2026 14:24:23 +0000

Every few months I install a small utility, and every few months something in its dependency tree ships a breaking change and the utility stops working. The tool was four lines of real logic wrapped in forty packages.

So I set a rule for a set of command-line tools: import nothing outside the Python standard library. Not "few dependencies" — none. If a machine has Python 3.9+, it runs. Here is what that constraint buys, and what it genuinely costs.

What the constraint buys

Nothing can rot. No transitive dependency can break my tool by releasing a major version.
It installs where pip does not. Air-gapped machines, locked-down corporate images, containers where you are not allowed to pull anything.
It is small enough to read. A few hundred lines you can audit in ten minutes beats a package you trust because it has stars.
It starts instantly. No import of a framework to convert a CSV file.

The seven tools

Typed CSV to JSON, JSON back to CSV, bulk renaming, duplicate detection, a link checker, Markdown to HTML, and a directory size report. Boring, recurring jobs.

Three places where the boring version is the hard version

Renaming files is a destructive operation. The naive os.rename loop will overwrite a file that happens to have the target name, and you find out later. So the rename tool builds a plan first, refuses any target that already exists, prints the plan, and only then acts. Every applied batch writes an undo log, so --undo replays the moves backwards. Destructive tools should default to dry-run.

CSV type inference is a trap. "1" might be a number, an ID, or a boolean. "true" might be a string in a column of strings. Empty is not the same as zero, and a spreadsheet saved on Windows starts with a UTF-8 BOM that will silently corrupt your first column name. My rule: convert only unambiguous scalars, treat empty as null, and give people a flag to turn inference off entirely.

Hashing everything is wasteful. Duplicate detection by SHA-256 means reading every byte of every file. Bucket by size first — two files of different sizes cannot be identical — then hash only within size groups. On a photo library this turns minutes into seconds.

Where the standard library is the wrong choice

I would not build image processing on it, or PDF parsing, or anything that needs a real HTTP client with retries and connection pooling. urllib is fine for checking whether a URL returns 200; it is not fine for scraping at scale. Pretending otherwise would be ideology over engineering. The constraint is worth it for small, dependency-hostile utilities, not for everything.

What I would do differently

I would write the tests before the type-inference code. The "1" vs true vs "yes" cases are exactly where bugs live, and the test suite caught behaviour I had already convinced myself was correct.

I packaged these tools. Two of them — Markdown to a single self-contained HTML file, and the disk usage report — are free, MIT licensed, source included, so you can judge the code before deciding anything: free lite edition.

The full set is $7 on the same store. I am the author, so treat the link as self-promotion; the technical parts above stand on their own regardless.
Source for the two free tools (MIT) is on GitHub: https://github.com/ClaireMananite/zdk

DEV Community: Claire Nagase