How to Find Hidden Characters in Excel
Two cells look identical but your lookup returns #N/A. The culprit is almost always an invisible character. Here's how to catch it.
Last updated: August 2026
| Cell (looks like) | Actually contains | Named | |
|---|---|---|---|
| Acme Ltd | Acme·Ltd␠ | → | trailing space |
| 1001 | 1001 (with 160) | → | non-breaking space |
Method 1 — LEN vs CLEAN/TRIM (any version)
- Beside your data, enter
=LEN(A2)-LEN(TRIM(CLEAN(A2))). Any non-zero result means hidden spaces or non-printing characters are present. - This flags that something's wrong — but not what, or where in the string.
Method 2 — CODE / UNICODE per character
Read the number behind a suspect character with =CODE(MID(A2,n,1)) or =UNICODE(MID(A2,n,1)). Useful values: 160 non-breaking space, 9 tab, 10/13 line breaks, 8203 zero-width space. Precise, but you're checking one position at a time — tedious across a real dataset.
The one-click way — Tellsheet's Find Hidden Characters
Click Find Hidden Characters and Tellsheet adds a column that names the invisible characters hiding in each row — non-breaking spaces, zero-width characters, tabs, line breaks, control characters, stray leading/trailing spaces. It's the "why won't my lookup match?" detective: your data isn't changed, you just finally see what's there. Then clean it with Remove Extra Spaces or Remove Special Characters. Works on Excel for Windows, Mac, and the web.
Frequently asked questions
Why won't my VLOOKUP match identical-looking text?
A hidden character — trailing space, non-breaking space (160), or a zero-width character — makes two look-alike values differ byte for byte. Remove it and the match works.
How do I see non-printing characters?
Detect them with LEN(A2)-LEN(TRIM(CLEAN(A2))), identify with CODE/UNICODE, or let Tellsheet's Find Hidden Characters name each one in a column.
What is character 160?
A non-breaking space, common in text pasted from the web. TRIM won't remove it — which is why cells look clean but still won't match.
Related Excel guides
See what's really in the cell
Get Tellsheet and name every hidden character in one click — the fastest way to fix a lookup that won't match. Nothing leaves your PC.
Get Tellsheet free See pricing