How to Remove Spaces in Excel: TRIM, CLEAN, SUBSTITUTE
· 7 min read
To remove spaces in Excel, use TRIM. It deletes every space at the start and end of the text, and shrinks any run of spaces inside it to a single space:
=TRIM(A2)That fixes most imported data. If you need every space gone, including the ones between words, use SUBSTITUTE instead. And if TRIM seems to do nothing, the cell probably holds a line break or a non-breaking space, which TRIM does not touch. This guide covers all three cases.
Why extra spaces matter
You cannot see a trailing space, but Excel can. "Copy paper" and "Copy paper " are different text to every function that compares values. Lookups return #N/A, COUNTIF and SUMIF miss rows, and a pivot table shows the same product twice. LEN is the quickest way to see what is really in a cell, because it counts spaces too.
TRIM: the everyday fix
Four product names from an import. Each of the first three has spaces in a different place. Column B counts the characters before cleaning and column D after.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product (raw) | Length | Trimmed | Length after | Question | Result | |
| 2 | Copy paper | 12 | Copy paper | 10 | Copy paper in raw names | 0 | |
| 3 | Copy paper | 13 | Copy paper | 10 | Copy paper in trimmed names | 3 | |
| 4 | Copy paper | 12 | Copy paper | 10 | |||
| 5 | Paper cups | 10 | Paper cups | 10 |
Column B shows 12, 13, 12 and 10. After TRIM, every name is 10 characters: "Copy paper" with one space in the middle, and "Paper cups" unchanged. TRIM leaves clean text alone, so you can safely apply it to a whole column.
The counts in column G show why this matters. COUNTIF finds 0 rows of "Copy paper" in the raw names, because none of them match exactly. In the trimmed column it finds all 3.
Replace the original column
TRIM returns a new value; it does not change the cell it points at. To clean the data itself:
- Put
=TRIM(A2)in a spare column and fill it down. - Copy the new column.
- Right-click the original column and choose Paste Values.
- Delete the helper column.
If the raw data is refreshed from an export, keep the helper column instead and point your formulas at it. Or clean inside the formula that needs it: comparing or looking up TRIM(A2) instead of A2 leaves the import untouched. Our VLOOKUP guide lists spaces as one of the main reasons a lookup returns #N/A.
Excel formula to remove all spaces: SUBSTITUTE
Sometimes the spaces inside the text are the problem: phone numbers, account codes or amounts typed with spaces as separators. SUBSTITUTE replaces every copy of one piece of text with another. Replace a space with nothing:
=SUBSTITUTE(A2, " ", "")| A | B | C | D | |
|---|---|---|---|---|
| 1 | Value (raw) | TRIM | No spaces | Spaces in raw value |
| 2 | 0113 496 0123 | 0113 496 0123 | 01134960123 | 2 |
| 3 | GB29 NWBK 6016 1331 | GB29 NWBK 6016 1331 | GB29NWBK60161331 | 3 |
| 4 | 1 250 | 1 250 | 1250 | 1 |
| 5 | LS1 4AP | LS1 4AP | LS14AP | 1 |
C2 returns 01134960123 and C3 GB29NWBK60161331. TRIM changes nothing on these rows, because they have no leading, trailing or double spaces.
Row 4 is an amount typed with a space as the thousands separator. Removing the space gives the text "1250". To use it in a sum, convert it: =VALUE(SUBSTITUTE(A4, " ", "")) returns the number 1250. Phone numbers are different: keep them as text, or the leading zero disappears.
Row 5 is a postcode, where the space belongs. Removing it gives "LS14AP", which is wrong. Choose the function by what the data should look like: TRIM when single spaces between words are correct, SUBSTITUTE when no spaces should be there at all.
Column D uses a handy trick: the length minus the length without spaces is the number of spaces. It returns 2, 3, 1 and 1. Use it to find rows with unexpected spaces before you clean them.
Find and Replace
For a one-off clean-up, Find and Replace does the same as SUBSTITUTE without a formula. Select the column, press Ctrl+H, type a single space in Find what, leave Replace with empty and click Replace All. It changes the cells in place, so check the selection first. It will happily remove the space from every postcode too.
When TRIM does not remove the spaces
TRIM only removes the normal space character (code 32). Two other characters look like spaces and survive it.
Line breaks: TRIM keeps them
A line break inside a cell (Alt+Enter, or a newline from a web form) is character 10. TRIM leaves it in place, so a note can still fail a comparison after TRIM. CLEAN removes it, along with the other non-printing characters (codes 0 to 31). If the text is in B2:
=TRIM(CLEAN(B2))CLEAN deletes the line break; TRIM then tidies the spaces. One catch: CLEAN removes the break without putting a space in its place. "Leave at" on one line and "reception" on the next becomes "Leave atreception". When the break separates words, swap it for a space instead:
=TRIM(SUBSTITUTE(B2, CHAR(10), " "))Text pasted from some systems uses character 13 as well as 10. CLEAN removes both.
Non-breaking spaces from web pages
Text copied from a web page or a PDF often contains non-breaking spaces (character 160). They look identical to normal spaces, but neither TRIM nor CLEAN removes them. LEN still counts them, so a cell that should be 10 characters and stays 11 after TRIM is a good sign. Turn them into normal spaces first, then trim:
=TRIM(SUBSTITUTE(B2, CHAR(160), " "))If you are not sure which character is there, =CODE(RIGHT(B2, 1)) returns the code of the last character: 32 is a normal space, 160 a non-breaking one, 10 a line break.
Common mistakes
Trimming the lookup table but not the lookup value. Both sides of a match have to be clean. If the values you look up come from the messy import, clean them too.
Removing every space when you meant TRIM. SUBSTITUTE with "" turns "Copy paper" into "Copypaper". AI assistants sometimes suggest it for "remove spaces" without asking which spaces you mean. If the text has words in it, TRIM is almost always the right choice.
Assuming a cell of spaces is blank. A cell with one space looks empty, but it is not blank to COUNTBLANK, ISBLANK or a filter for empty cells. Check with LEN, which returns 1. Our COUNTIF guide covers counting blank cells.
Trusting TRIM to fix everything. If the lengths do not drop after TRIM, look for line breaks or non-breaking spaces as shown above.
Cleaning one column and forgetting the next refresh. If the data comes from a regular export, the spaces come back each time. Keep the cleaning in formulas, or in a Power Query step, rather than fixing the cells by hand. Note that the Trim step in Power Query only removes spaces at the start and end, not double spaces inside the text.
FAQ
How do I remove spaces in Excel?
Use =TRIM(A2) to remove spaces at the start and end and reduce double spaces to one. Paste the results as values over the original column if you want to keep them.
How do I remove all spaces in a cell?
Use =SUBSTITUTE(A2, " ", ""). It removes every space, including those between words, so only use it on codes and numbers.
Why is TRIM not working in Excel?
The cell probably contains non-breaking spaces (character 160) or line breaks (character 10). Replace them with SUBSTITUTE and CHAR, or use CLEAN for line breaks, then apply TRIM.
What is the difference between TRIM and CLEAN?
TRIM removes extra normal spaces. CLEAN removes non-printing characters such as line breaks. They are often used together.
How do I count the spaces in a cell?
Subtract the length without spaces from the full length: =LEN(A2)-LEN(SUBSTITUTE(A2, " ", "")).
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
- Strip the spaces off customer refs
- Make the lookup see past the spaces
- Remove hidden line breaks from notes
- The AI missed contacts with no emailAudit