How to Remove Special Characters and Line Breaks in Excel

Text pasted from websites, PDFs, emails and other systems carries characters that look harmless and break lookups, imports and sorting. A cell that seems to contain "ABC" may really hold a zero-width space in front of it, which is why =A2="ABC" says FALSE. The fix is to find out which character it is and remove exactly that.

Step 1: find out what is really in the cell

Compare what you see with what is there:

=LEN(A2)

If a cell showing "ABC" has a length of 4, something invisible is in it. To read the code of the first character, or the nth:

=UNICODE(MID(A2,1,1))

Change the 1 to look at each position. Ordinary letters and digits are below 128.

Step 2: the common culprits

A byte order mark at the start of a CSV file usually shows as  in the first header, and it can stop the first column from being recognised.

Step 3: CLEAN for non-printing characters

=CLEAN(A2)

CLEAN removes the first 32 non-printing characters, such as line breaks. It does not remove the non-breaking space or the zero-width space, so combine them:

=TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE(A2,UNICHAR(160)," "),UNICHAR(8203),"")))

Step 4: Find and Replace for the whole sheet

Press Ctrl+H. To remove line breaks, click in "Find what" and press Ctrl+J (nothing visible appears, which is normal), type a space or nothing in "Replace with", and choose Replace All. To remove a literal asterisk or question mark, type ~* or ~?, because the tilde tells Excel the next character is not a wildcard.

Step 5: keep only letters, digits and spaces (Microsoft 365)

If you want to strip everything else, for example from a reference code:

=LET(t,A2,c,MID(t,SEQUENCE(LEN(t)),1),CONCAT(IF(ISNUMBER(FIND(c,"ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789 ")),c,"")))

This removes accented letters too, so do not use it on names. It needs a version of Excel with LET, SEQUENCE and CONCAT (Microsoft 365 or 2021).

Paste the result as values

Formulas live in a helper column. When the result looks right, copy it and use Paste Special, Values over the original, then delete the helper column.

A whole file at once

DecodingPro cleans stray whitespace and invisible characters in text cells and repairs garbled characters where the cause is clear, and it lists each change so nothing is altered silently.

Questions people ask