Remove Invisible Characters in Excel
Not Excel? Try the zero-width space remover for ordinary text, the JSON cleaner for payloads, or the Word and Docs cleaner for document text. The full character list with copy buttons is on invisible Unicode characters.
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.
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.
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.
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:
| Decimal | Codepoint | Name |
|---|---|---|
| 173 | U+00AD | SOFT HYPHEN |
| 160 | U+00A0 | NO-BREAK SPACE |
| 8203 | U+200B | ZERO WIDTH SPACE |
| 8204 | U+200C | ZERO WIDTH NON-JOINER |
| 8205 | U+200D | ZERO WIDTH JOINER |
| 8206 | U+200E | LEFT-TO-RIGHT MARK |
| 8207 | U+200F | RIGHT-TO-LEFT MARK |
| 8288 | U+2060 | WORD JOINER |
| 65279 | U+FEFF | ZERO WIDTH NO-BREAK SPACE (BOM) |
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.