Cleaning up text that came from somewhere else

Text that arrived from a PDF, a spreadsheet cell, a CMS field, an email client or a chat app carries the formatting decisions of whatever produced it, and almost none of those decisions are visible on screen. The result is a string that looks correct, prints correctly, and then fails a comparison, a search, a JSON parse or a database lookup for no apparent reason.

Characters you cannot see

These are real characters to Unicode. They simply have no visible shape, or the same shape as something else.

The reason a search fails is that a search compares code points, not shapes. If a PDF gave you New U+00A0 York and you type New York with an ordinary space U+0020, the two strings are different and nothing will tell you why.

"New York".includes("New York")   false, the gap is U+00A0
"  text​".trim().length      5, the zero-width space survives

A non-breaking space counts as whitespace to most trim functions and to \s in most regex engines, so it disappears at the edges of a string and survives in the middle, exactly where a split expects a plain space. A zero-width space is not whitespace anywhere, so trimming and collapsing leave it untouched. It is also why Email Extractor can return nothing from text copied out of a mail client: one zero-width character inside the address stops the pattern matching.

Hidden Character Detector shows what is actually there, and Unicode Escape gives the exact code points of a single value. Do not strip everything on sight, though: a zero-width joiner inside an emoji sequence and a zero-width non-joiner in Persian or Devanagari are content, not noise.

Punctuation a word processor changed for you

Autocorrect substitutes typographic characters as you type, and the substitution follows the text out of the document.

That is fine in prose and fatal in anything a machine parses.

{ “name”: “Ada” }      unexpected token, only U+0022 is a JSON string quote
git commit -m “fix”    the shell sees three words, not one quoted argument
WHERE name = ‘Ada’     SQL syntax error near ‘
10\u201320                  not a number range, U+2013 is not a hyphen

In CSV the failure is quieter. A parser recognises only the straight double quote as a field delimiter, so a value that a word processor wrapped in curly quotes is treated as unquoted; any comma inside it then splits the row and every column after it shifts. A column whose negatives use U+2212 imports as text, and sums silently exclude it.

Find and Replace with a short substitution list is the fix, but apply it only to text headed for code, CSV or a key. Running it over prose you are publishing flattens punctuation that was deliberate.

Line endings and trailing whitespace

Windows tools end a line with CR LF (U+000D U+000A), Unix tools with LF alone, and a few very old Mac exports still use CR alone. Most parsers cope. Naive splitting does not.

"UK\r\n" split on "\n"  ->  "UK\r"
"UK\r" === "UK"             false
"UK\r".length               3

Two strings that render identically in a table, a log or a diff view can still differ by a carriage return, a trailing space or a tab. When Text Diff marks a line as changed and no change is visible, that is the answer. It is also what puts a stray space inside the quotes when a pasted column goes through Quote Lines or Join Lines.

Normalise in one pass, matching CR LF and a lone CR together (\r\n? replaced with \n), otherwise a two-step replacement doubles up the blank lines.

One letter, two ways to write it

Unicode allows an accented letter to be a single code point or a base letter followed by a combining mark. Both are correct, and they are not equal.

macOS has historically handed out decomposed filenames, and several PDF generators and input methods emit decomposed text, so a value from one source will not match the same value typed on a keyboard.

"é" === "é"                  false
"é".normalize("NFC") === "é" true

The knock-on effects go past equality. A sort that compares code points puts decomposed forms next to plain e and composed forms far away, so a list of names comes back in two clumps. Character counts differ between the forms, which matters for a 280 character limit or a VARCHAR(50) column, and Text Statistics reports different numbers for what looks like the same text. Truncation is worse: cutting a decomposed string at a fixed length can slice between a base letter and its accent, leaving that accent to attach to whatever follows, so Truncate Text on unnormalised input can produce a visibly wrong last character.

The compatibility forms, NFKC and NFKD, go further and fold characters that merely resemble others: the ligature U+FB01 becomes fi, full-width Ａ U+FF21 becomes A, the micro sign U+00B5 becomes Greek mu U+03BC, and superscript ² becomes a plain 2. That is excellent for a search or deduplication key and destructive for anything you display, because x² quietly becomes x2.

The working rule: NFC for storage and display, NFKC only for keys nobody ever sees.

Case conversion is not one operation

Uppercasing is not a per-character mapping, and it is not locale-free.

• German ß U+00DF uppercases to SS, so the string gets longer and a round trip back to lowercase does not return the original.
• Greek sigma lowercases to ς at the end of a word and σ elsewhere, which again breaks the round trip.
• Turkish and Azerbaijani have a dotless ı U+0131 and a dotted capital İ U+0130. In those locales I lowercases to ı and i uppercases to İ.

That last one is the classic production bug. Code that lowercases a header name, a file extension or a protocol string works everywhere until it runs on a machine whose default locale is Turkish, where "FILE" lowercases to fıle and stops matching file. In Java and .NET the no-argument methods use the machine's default locale, so toUpperCase() and ToUpper() are the trap; name an invariant locale for anything machine-readable. For comparison, case folding (casefold() in Python) beats lowercasing, because it handles ß as ss.

Title case has no single definition, which is why "capitalise every word" produces "The Lord Of The Rings" and "IPhone". Most style guides capitalise the first and last words plus everything except articles, coordinating conjunctions and short prepositions, keep both halves of a hyphenated compound capitalised, and never touch acronyms or names with internal capitals such as McDonald, O'Brien or iPhone.

Programming cases have their own boundary problem. Converting HTTPResponseCode to snake case depends entirely on how the splitter treats a run of capitals; Case Converter gives http_response_code, a naive one gives h_t_t_p_response_code.

An order that works

Each step assumes the previous one has run. Out of order, they fight.

1. Settle the encoding. Mojibake such as café means the bytes were decoded as the wrong encoding, so re-decode the original bytes rather than patching the symptoms. Remove a BOM only at the very start of the text.
2. Normalise line endings to LF in a single pass.
3. Remove format characters you have judged to be noise: soft hyphens, zero-width space, word joiner, bidi marks. Do this before normalising, because NFC cannot compose a base letter and its accent across an invisible character sitting between them.
4. Replace lookalike punctuation, if the destination is code, CSV, JSON or a key.
5. Apply Unicode normalisation, NFC as the default.
6. Now handle whitespace. Convert the remaining exotic spaces to U+0020, collapse runs, then trim. Doing this before step 3 leaves double spaces wherever a non-breaking space met an ordinary one, and trimming before step 2 leaves a carriage return glued to the last value on every line.
7. Convert case last, with the locale named explicitly.

Word-level operations belong after step 3 too. Splitting text with Split Text while soft hyphens remain gives inflated counts and half-words, and the same applies to anything scanning for terms, including Censor Text.

When a value still refuses to match after all of that, stop looking at it. Print its length, escape it to code points, and compare the two escaped forms directly; the difference is obvious there in a way it never is on screen.