Remove Invisible Characters in Excel

Why CLEAN() and TRIM() do not fix this

CLEAN() removes the first 32 characters of the ASCII set, codes 0 through 31. Those are the control characters from the era of 7-bit text. A zero width space is Unicode U+200B, decimal 8203, which is nowhere near that range, so CLEAN() leaves it exactly where it was.

TRIM() collapses repeated spaces and strips them from the ends. It also does not touch a non-breaking space, because that is U+00A0, decimal 160, which is not the space character TRIM() is looking for.

That is why the usual advice fails on data exported from a web application or a financial system: the characters causing the trouble were never in the range those two functions cover.

Three things called TRIM, and they do not agree

The worksheet TRIM() does two jobs at once. It takes spaces off both ends and it collapses every run of spaces inside the text down to a single one, which is not part of TRIM anywhere else. In VBA, Trim() touches the ends and leaves the middle exactly as it was; to get the worksheet behaviour out of a macro you have to ask for WorksheetFunction.Trim by name. In SQL, TRIM is narrower than either.

Measured here on SQLite: trim(X) removes U+0020 and nothing else. A tab stays. A line feed stays. A carriage return stays. So does a non-breaking space, a zero width space, a byte order mark, U+180E and an ideographic space — five characters out of five still present after the call, all five invisible on screen.

SQL will take the set from you instead: trim(X, Y) strips whatever is in Y. Given a set made of a plain space, U+00A0, U+200B, U+FEFF, U+180E and U+3000, all five come off the ends. A soft hyphen does not, and U+202D does not, because neither one was named in the set. What you hand it is the entire definition of what counts as dirt.

Which means a column trimmed in Excel and then trimmed again on its way into a database has been through two different functions that share a name, and the second is the weaker of the two. CLEAN() has the same shape of trouble at the other end of its range: it stops at 31, so CHAR(127) — the first thing people reach for once the obvious ones fail — sits outside it and comes back untouched.

How to tell whether a cell has one

The length is the tell. Compare the raw cell against a cleaned version:

=LEN(A1)<>LEN(SUBSTITUTE(A1,UNICHAR(8203),""))

TRUE means cell A1 contains at least one zero width space. To find out which character it actually is, use =UNICODE(MID(A1,n,1)) and change n until you land on a number you do not expect — 8203, 8288, 173, 65279 and 160 are the usual suspects.

The formula that does work

Nest one SUBSTITUTE per character. This one clears the five most common and replaces a non-breaking space with a normal one instead of deleting it:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,UNICHAR(8203),""),UNICHAR(8288),""),UNICHAR(173),""),UNICHAR(65279),""),UNICHAR(160)," ")

Fill it down the column, then copy the result and paste it back over the original as values. Add another SUBSTITUTE(...,UNICHAR(8206),"") layer if your data comes from right-to-left text.

The 122-code list, checked one code at a time

The answer most often copied for this job is not a formula. It is a macro carrying a list of 122 decimal codes, and it travels under headings like "remove all special characters" into forums, internal wikis and company templates. I ran all 122 through the Unicode character database (Python 3.13, Unicode 15.1) and sorted them by what they actually are.

Then there are the gaps, and they fall exactly where real data lives. The list runs from 159 straight to 1970, so decimal 160 (U+00A0) and decimal 173 (U+00AD) are not on it. It carries 8289, 8290, 8291 and 8292 but not 8288, so U+2060 is missing as well. A non-breaking space, a soft hyphen and a word joiner are three of the characters most often found in text pasted out of a browser, a PDF or a web application, and the list that promises to remove everything invisible steps over all three.

One of the 22 deserves its own note, because the standard changed its mind about it. Decimal 6158 is U+180E, the Mongolian vowel separator. In the Unicode character database up to and including version 6.2 it is filed as a space separator, category Zs; from version 6.3 onward it is a format character, category Cf. On current data it is not whitespace to Python: isspace() returns false and strip() leaves it where it is. Its three neighbours in the list, 6155 to 6157, are not invisible at all — they are the free variation selectors, and their whole job is to pick a glyph.

The one-line fix that takes the punctuation with it

When a merge refuses to match two columns that look identical, the remedy usually offered is a range instead of a list: strip everything from U+2000 to U+206F and be done with it. That range is 112 code points. Twenty-five of them are format characters. Fifteen more are spaces and separators. One is unassigned. The remaining seventy-one are punctuation people typed on purpose — en and em dashes, curly quotes in four shapes, the ellipsis, the bullet, the prime marks, the daggers.

Anyone who has run that one-liner over a column of prose has watched the quotes straighten and the dashes disappear. It works, in the sense that the invisible characters are gone. So is a lot else.

Or use Find and Replace with the character itself

Sometimes a formula is overkill. Copy the actual character from the character list, press Ctrl+H in Excel, paste it into Find what, leave Replace with empty and hit Replace All. Because the character is invisible, the Find box will look empty — it is not.

What the workbook file does to the character first

An .xlsx file is a zip archive of XML parts, and the text of your cells sits in one of them. XML 1.0 does not admit every code point: the legal set is tab, line feed, carriage return, and everything from U+0020 upward. Two conforming parsers here — the one behind Python's ElementTree and the one behind minidom — were each handed a one-character document. Both accepted the tab, the line feed, the carriage return and U+007F. Both refused U+000B, U+000C, U+001C, U+001D, U+001E and U+001F.

All six are in the 122-code list, as decimals 11, 12, 28, 29, 30 and 31. A cell holding CHAR(11) or CHAR(12) cannot be written into a conforming workbook as itself. It gets dropped, it gets replaced, or it gets spelled some other way — and whichever of those happens, it is no longer the value you typed.

The writing side is the less careful of the two. Python's own XML serializer, handed a vertical tab, writes it straight out: the element comes back as <t>, the raw byte, </t>, with nothing escaped and no warning. The file it produces is one that no conforming reader will open.

What a single-byte export does to it

Text has to be encoded before it can be written to a file, and the invisible characters are the ones with no seat reserved for them. Of seventeen of them tried against Windows-1252 here, three have a byte: U+00A0, U+00AD and the en dash. The other fourteen — zero width space, word joiner, both joiners, the directional overrides, the BOM, U+2028, U+2029, U+3164, U+3000 and the rest — have none. A writer that does not halt on them substitutes a question mark, so the character you could not see turns into one you can, and into a new reason for every lookup against that column to miss.

The way back is quieter and worse. Save as UTF-8, open the file as though it were ANSI, and each invisible character returns as two or three perfectly ordinary letters: U+200B comes back as ​, U+FEFF as . U+2060 is where it stops being merely cosmetic: its bytes are E2 81 A0, and 81 means nothing in Windows-1252, so a reader that does not guess raises an error on the file instead of mangling it.

Decimal codes for UNICHAR

UNICHAR takes a decimal number, not a hex codepoint, which is the part people get stuck on. These are the ones worth knowing:

Using the box above

Select the column, copy it, paste it into the box on the left and press Clean cells. Line breaks are kept, so one output line still matches one input cell, and you can copy the result straight back over the original column.

Do it one column at a time. Pasting a whole rectangular range would flatten it into lines.

Is anything uploaded?

No. There is no backend. The cleaning runs in this browser tab and your data never leaves your device, which matters when the column is customer data.