How to Remove Spaces in Excel With TRIM (and When It Fails)

Stray spaces are invisible, which is exactly why they cause trouble. A client name pasted from a web form with a space on the end won't match the same name in your other list, a lookup returns nothing, and a count comes up short. TRIM is the fix Microsoft recommends for text that arrives from another application with irregular spacing, and it takes about a minute to run on a whole column.

Cleaning a column with TRIM

1. Insert a blank column next to the messy one. If the names are in column A, use column B.
2. In B2, type =TRIM(A2) and press Enter.
3. Double-click the small square at the bottom right of B2 to copy the formula down the list.
4. Select column B, copy it, then right-click A2 and choose Paste Values so the clean text replaces the original.
5. Delete column B.

Microsoft describes TRIM as removing all spaces from text except single spaces between words. So Sarah Jones becomes Sarah Jones, with one space in the middle and none at either end.

What TRIM removes and what it leaves

Problem	Does TRIM fix it?	What to use
Spaces at the start	Yes	=TRIM(A2)
Spaces at the end	Yes	=TRIM(A2)
Double spaces between words	Yes, down to one	=TRIM(A2)
Non-breaking spaces from web pages	No	Microsoft's data-cleaning guide
Line breaks and hidden characters	No	=TRIM(CLEAN(A2))
Every space, including between words	No	Find and Replace

Why TRIM sometimes does nothing

This is the catch that sends people round in circles. Microsoft explains that TRIM was designed to remove the ordinary space character. Web pages often use a different one, the non-breaking space, and Microsoft says TRIM on its own doesn't remove it. So if you've copied a list from a website and TRIM leaves it unchanged, that's almost certainly why.

Microsoft gives the non-breaking space a decimal value of 160 and, rather than spelling out a fix on the TRIM page, points to its separate guide on the top ten ways to clean your data for an example that removes both kinds of space. If you only have a handful of cells, retyping the gap between words is often quicker than building a formula.

For line breaks and other non-printing characters, Microsoft's example combines two functions: =TRIM(CLEAN(A2)) removes the spaces and cleans the string in one go.

Removing every single space

TRIM keeps single spaces between words, which is what you want for names. For things like product codes or phone numbers, you might want no spaces at all. Use Find and Replace instead:

1. Select the column.
2. Press Ctrl+H.
3. In Find what, type a single space. Leave Replace with empty.
4. Select Replace All.

Be careful running that on a column of names, because Sarah Jones becomes SarahJones. And if your codes start with zeros, check they survive; Excel can treat a code with no spaces as a number and drop the leading zeros.

Where stray spaces break things

• Counting. Microsoft warns that leading and trailing spaces can make COUNTIF return unexpected results, and suggests TRIM or CLEAN.
• Sorting. Microsoft also says data imported from other apps may carry leading spaces, and these should be removed before you sort, or those rows sort to the top.
• Lookups. A client called Smith with a trailing space won't match Smith in another sheet.
• Mail merges. Extra spaces show up in greetings, like Dear Sarah.

Once your text is clean, the numbers next to it behave too. If you're working out items per order from a cleaned-up list, my guide to dividing in Excel covers that. And while you're tidying, my guide to deleting blank rows in Excel clears the empty gaps that exports leave behind.

Should you keep the TRIM formula?

Usually not. A TRIM column depends on the original column, so if you delete the original, the cleaned text disappears. That's why step 4 above pastes values. The exception is a sheet you refresh regularly from an export: there, I'd keep the raw data on one tab and a TRIM column on another, so every new paste is cleaned automatically.

Spotting spaces before they cause trouble

You can't see a trailing space, but you can test for one. Microsoft says LEN returns the number of characters in a text string, and spaces count as characters. Put =LEN(A2) next to a name and compare it with =LEN(TRIM(A2)). If the first number is bigger, the cell has spaces TRIM would remove. Copy both down a column and any row where the numbers differ needs cleaning. It's a quick check before you run a lookup or a count on a list you didn't build.

Another giveaway is a name that looks identical to one in another list but won't match. Click into the cell, press End, and see whether the cursor stops a little way after the last letter. That gap is a space, and it's the kind of thing that breaks a mail merge or a lookup without any warning at all. Once you've spotted the pattern in one row, it's usually in hundreds, so clean the whole column rather than fixing cells by hand.

My verdict

TRIM in a helper column, paste values, delete the helper. If TRIM doesn't seem to work, you've got non-breaking spaces from a web page, so add SUBSTITUTE. I'd run it on every list that comes from a form or an export before I do anything else with it, because it saves so much head-scratching later.

Frequently asked questions

How do I remove spaces in Excel?

Use =TRIM(A2) in a spare column, copy it down, then paste the results back as values.

Does TRIM remove spaces between words?

It reduces them to a single space. It removes spaces at the start and end completely.

Why isn't TRIM removing spaces?

The text probably contains non-breaking spaces from a web page, which TRIM doesn't remove on its own. Microsoft's data-cleaning guide covers how to remove those as well.

How do I remove all spaces in Excel?

Select the cells, press Ctrl+H, type a space in Find what, leave Replace with empty and select Replace All.