Why do these characters break Excel?
They look like spaces or nothing at all, but Excel treats each as a different character. A lookup for "Acme Inc" will not match "Acme" + non-breaking space + "Inc". The cells look identical, so the failure feels random.
| Character | Code | Formula |
|---|---|---|
| Non-breaking space | 160 | =SUBSTITUTE(A1, UNICHAR(160), " ") |
| Narrow no-break space | 8239 | =SUBSTITUTE(A1, UNICHAR(8239), " ") |
| Zero-width space | 8203 | =SUBSTITUTE(A1, UNICHAR(8203), "") |
| Soft hyphen | 173 | =SUBSTITUTE(A1, UNICHAR(173), "") |
| Byte order mark | 65279 | =SUBSTITUTE(A1, UNICHAR(65279), "") |
One formula for the common cases
Nest the replacements, then trim:
=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, UNICHAR(160), " "), UNICHAR(8239), " "), UNICHAR(8203), ""))
Fill it down a helper column, copy the results, and Paste Special as Values over the original. UNICHAR is available in Excel 2013 and later.
Or clean the text before it reaches the sheet
Copy the column, paste it into the cleaner above, and copy the result back. The report lists every non-breaking space, zero-width character and soft hyphen it found, which also tells you where the data came from. See removing non-breaking spaces and removing zero-width spaces for the characters themselves.
Pasting ChatGPT or web text into Excel
Multi-line text pasted into one cell may spill across rows. Paste into the formula bar to keep it in one cell, or use Alt+Enter for a line break you type. AI text also contains the narrow no-break space more often than most sources. For the wider problem of copied text that will not behave, see sanitizing copied text for code, JSON and CSV.