Remove spaces and invisible characters in Excel: TRIM, CLEAN and the non-breaking space

“Customer A” and “Customer A ” look identical, but to Excel they are two different texts. That invisible difference makes VLOOKUP return #N/A, makes a PivotTable show the same customer twice or lets duplicate removal leave repeated rows behind.

Extra spaces and hidden characters almost always arrive with data copied from web pages, exported from other systems or typed by hand. Here you will see which characters cause trouble, how to detect them and how to clean them with TRIM, CLEAN and SUBSTITUTE, including the most elusive one: the non-breaking space.

Which invisible characters cause trouble

Not all “spaces” are equal. Each character has a code, and knowing which one you are dealing with decides which function removes it:

How to detect spaces and hidden characters

Before cleaning, it helps to know how many cells are affected. These checks tell you in seconds.

Compare lengths

LEN counts every character, invisible ones included. If the length changes when you apply TRIM, the cell had extra spaces:

=LEN(A2)-LEN(TRIM(A2))

The formula returns how many spaces are extra: any result greater than 0 flags a cell you need to check. You can also use it as a rule in Home › Conditional Formatting › New Rule › Use a formula to determine which cells to format to highlight them in color.

Identify the exact character

If TRIM changes nothing but the cell still does not match, find out the code of the last character:

=UNICODE(RIGHT(A2,1))

A 160 means a non-breaking space; a 10, a line feed. Swap RIGHT for LEFT to check the first character. UNICODE is available from Excel 2013; in earlier versions use CODE.

Compare two cells exactly

When two values look the same and do not match, =A2=B2 returns FALSE even though you cannot see the difference. Compare their lengths with LEN: the one with more characters is hiding something.

TRIM: removes extra spaces

TRIM does three things at once:

• Removes spaces at the start of the text.
• Removes spaces at the end.
• Reduces repeated spaces between words to a single one.

=TRIM(A2)

The last row shows its limit: TRIM only removes the regular space (code 32). The non-breaking space stays.

CLEAN: removes control characters

CLEAN removes non-printable characters with codes 0 to 31, including line feed, carriage return and tab:

=CLEAN(A2)

Be careful with line breaks: CLEAN deletes them without leaving anything in their place, so “Main Street” on one line and “Unit 3” on the next become “Main StreetUnit 3”. If you would rather separate them with a space, replace the line feed first:

=TRIM(SUBSTITUTE(A2,CHAR(10)," "))

The non-breaking space and other tricky cases

The non-breaking space (code 160) is the usual culprit when nothing works. Neither TRIM nor CLEAN removes it. The fix is to turn it into a regular space first with SUBSTITUTE:

=TRIM(SUBSTITUTE(A2,CHAR(160)," "))

In Excel for Mac, CHAR uses a different character set, so it is safer to use UNICHAR (Excel 2013 and later), which works the same on every platform:

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

The zero-width space (code 8203) does not even take up room on screen. To remove it, replace it with nothing:

=SUBSTITUTE(A2,UNICHAR(8203),"")

The complete cleaning formula

Putting it all together, this formula solves the vast majority of cases in one go:

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

If the result should be a number, wrap it in VALUE. Remember that after cleaning you need to copy the helper column and paste it as values over the original.

Cleaning without formulas

Find and Replace

For a quick fix directly on the original data:

1. Select the range and go to Home › Find & Select › Replace.
2. In “Find what”, enter the character you want to remove. For a line break, press Ctrl+J. For a non-breaking space, hold Alt and type 0160 on the numeric keypad.
3. In “Replace with”, type a space or leave it empty.
4. Click Replace All.

Do not replace every regular space with nothing: you would glue the words together. For those, use TRIM.

Power Query

If you repeat the same cleanup with every file, Power Query (Excel 2016 and later) saves it as a step. Select the column and, under Transform › Format, choose Trim and Clean. Note: Power Query’s Trim only removes spaces at the ends, not double spaces in the middle.

Common mistakes

• Keeping the formulas instead of the values: if you delete the original column, the clean column fills up with #REF!.
• Expecting CLEAN to remove code 160: it does not; you need SUBSTITUTE.
• Cleaning only one of the two tables: if you are going to match data with VLOOKUP or XLOOKUP, clean both the lookup column and the values you are looking up.
• Forgetting the headers: a trailing space in a column name also breaks references and PivotTables.

After cleaning: duplicates and empty rows

When you clean, you often find that values that looked different were actually the same, and that some rows held nothing useful. If you are left with blank rows scattered across the sheet, our free tool to remove empty rows deletes them from the whole file at once, without sorting or filtering by hand. And for records that now repeat, you can use the duplicate remover.

Conclusion

Invisible characters cannot be seen, but they break lookups, comparisons and counts. Detect them by comparing LEN before and after TRIM, identify the character with UNICODE and remove it with the right combination: TRIM for regular spaces, CLEAN for control characters and SUBSTITUTE for the non-breaking and zero-width spaces.

Frequently asked questions