How to Remove Spaces in Excel

That last formula solves the case that wastes the most time, because nothing looks wrong. The cell shows what appears to be ordinary text, TRIM runs without error, and the value still refuses to match anything.

What TRIM actually does

TRIM removes spaces from the start and end of text, and collapses runs of internal spaces down to one. It is designed for tidying text that came from somewhere else.

Input	=TRIM() gives
" John Smith"	"John Smith"
"John Smith "	"John Smith"
"John   Smith"	"John Smith"
"John Smith"	"John Smith" (unchanged)

Two limits worth knowing. TRIM keeps single spaces between words — by design, since you rarely want "JohnSmith". And it only recognises character 32, the standard space. Anything else it leaves untouched.

The non-breaking space problem

Character 160 is a non-breaking space, used throughout HTML to stop lines wrapping. It renders identically to a normal space and arrives with anything pasted from a web page, a PDF, or many system exports.

TRIM does not touch it. Neither does a plain Find and Replace typed with the space bar, because you are searching for a different character.

Confirm it before fixing. =CODE(MID(A1,4,1)) returns the character code at position 4. If it returns 160 rather than 32, that is your answer.

Then convert and trim in one step with SUBSTITUTE:

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

Choosing the right approach

You want to	Use
Tidy stray spaces, keep words separated	=TRIM(A1)
Remove every space, including between words	=SUBSTITUTE(A1," ","")
Handle web or PDF text	=TRIM(SUBSTITUTE(A1,CHAR(160)," "))
Also strip line breaks and control characters	=TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," ")))
Remove all spaces from a column, no formula	Find and Replace

The four-function version handles essentially every dirty-data case: CLEAN strips non-printable characters such as line breaks, SUBSTITUTE converts non-breaking spaces, TRIM removes what remains.

Find and Replace, without a helper column

To strip every space from a range directly:

1. Select the range first. Skipping this searches the entire sheet.
2. Press Ctrl + H.
3. In Find what, press the space bar once.
4. Leave Replace with completely empty.
5. Click Replace All.

This removes all spaces including those between words, so it suits codes and IDs rather than names. It also cannot remove non-breaking spaces — to do that, paste a real one into the Find box by copying it from a problem cell, or run the SUBSTITUTE formula first.

Making the change permanent

A formula in a helper column produces clean text, but the original column still holds the messy version. To finish the job:

1. Select the helper column and copy it.
2. Select the original column.
3. Right-click → Paste Special → Values.
4. Delete the helper column.

Paste Values is the step that matters. An ordinary paste copies the formulas, which then reference cells you are about to delete — leaving a column of #REF! errors where your clean data should be.

Why this usually matters

Stray spaces rarely cause visible problems. They cause invisible ones: lookups that fail on values you can see, duplicates that are not detected as duplicates, and totals that omit rows.

If a VLOOKUP returns #N/A on a value that is plainly in the table, whitespace is one of the two usual causes — see why VLOOKUP cannot find a match that exists for the other.

Once the text is clean, how to split cells in Excel covers separating it into columns — including the step that stops Text to Columns overwriting the data beside it.

Frequently asked questions

How do I remove spaces in Excel?

Use =TRIM(A1) to remove leading, trailing and repeated spaces while keeping single spaces between words. To remove every space including internal ones, use =SUBSTITUTE(A1," ","") or Find and Replace with a space in Find and nothing in Replace.

Why is TRIM not removing my spaces?

Because the character is not a standard space. Text from web pages and PDFs often contains non-breaking spaces (character 160), which TRIM ignores. Use =TRIM(SUBSTITUTE(A1,CHAR(160)," ")).

How do I remove all spaces including between words?

=SUBSTITUTE(A1," ",""), or select the range, press Ctrl + H, put one space in Find what and leave Replace with empty. Suitable for codes and IDs rather than names.

What is the difference between TRIM and CLEAN?

TRIM removes extra spaces. CLEAN removes non-printable characters such as line breaks. They handle different problems, so dirty imports often need both: =TRIM(CLEAN(A1)).

How do I check what character is in my cell?

=CODE(MID(A1,4,1)) returns the character code at position 4. A standard space is 32 and a non-breaking space is 160. Comparing =LEN() on two values that look identical also reveals hidden characters.

How do I remove line breaks from a cell?

=CLEAN(A1) removes them along with other non-printable characters. In Find and Replace you can enter a line break in the Find box with Ctrl + J, which appears blank but works.

Do I need to keep the helper column?

No — copy it, select the original column, and use Paste Special → Values, then delete the helper. An ordinary paste copies formulas that reference the column you are deleting, producing #REF! errors.

Conclusion

TRIM for tidying, SUBSTITUTE for removing every space, and TRIM(SUBSTITUTE(A1,CHAR(160)," ")) when TRIM appears to do nothing. That last one covers web and PDF text, which is where most of this trouble originates.

Then paste as values before deleting the helper column — otherwise you replace clean data with errors.

Related: why VLOOKUP cannot find a match covers what whitespace breaks, and how to remove blank rows covers cleaning up structure rather than text.