=TRIM(A2) removes every space before and after the text in A2 and reduces each run of spaces between words to a single space. " Ana Silva " becomes Ana Silva.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name | TRIM | Length before | Length after |
| 2 | Ana Silva | Ana Silva | 14 | 9 |
| 3 | Ben Okafor | Ben Okafor | 13 | 10 |
| 4 | Chen Wu | Chen Wu | 10 | 7 |
The spaces are invisible in column A, which is why the length columns are there: LEN counts them. Extra spaces usually arrive with imported or pasted data, and they break lookups and comparisons without any visible sign.
Why extra spaces break formulas
To Excel, Ana with a trailing space and Ana are different texts. A comparison returns FALSE, COUNTIF does not count the cell, and a lookup returns #N/A:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name | Score | Is it Ana? | Ana's score |
| 2 | Ana | 90 | FALSE | not found |
| 3 | Ben | 85 | TRUE | 90 |
| 4 | Chen | 78 |
C2 is FALSE and D2 finds nothing (without its fourth argument XLOOKUP would show #N/A) because A2 holds Ana with a space. D3 trims the whole lookup column inside the formula and finds 90. That works in Microsoft 365 and Excel 2021; in older versions, clean the column first. Spaces are one of the most common causes of #N/A from a lookup.
Remove all spaces
TRIM always keeps one space between words. To remove every space, for example from a phone number or a product code, substitute the space with nothing:
| A | B | C | |
|---|---|---|---|
| 1 | Code | TRIM | No spaces |
| 2 | AB 12 34 | AB 12 34 | AB1234 |
| 3 | 555 201 3344 | 555 201 3344 | 5552013344 |
| A | B | |
|---|---|---|
| 1 | Customer | Clean name |
| 2 | Eli Novak | |
| 3 | Fay Ruiz | |
| 4 | Gus Lee |
Your turn: The names in column A have extra spaces. In B2, return the name in A2 with no spaces around it and single spaces between words. The formula fills down to B4.
TRIM not working: non-breaking spaces
Text copied from a web page or a PDF often contains non-breaking spaces, character 160, instead of normal spaces, character 32. They look identical, but TRIM only removes character 32, so in Excel nothing changes:
A2: Ana Silva (a non-breaking space between the names and one at the end)
=LEN(A2) 10
=LEN(TRIM(A2)) 10, TRIM removed nothing
The fix is to turn each character 160 into a normal space with SUBSTITUTE first, and let TRIM handle the rest. The text in A2 below is built with CHAR(160) so you can see the character a web page pastes:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Pasted text | Length | Code of 4th character | Fixed | Fixed length |
| 2 | Ana Silva | 10 | 160 | Ana Silva | 9 |
C2 is how you identify an odd character: CODE(MID(A2,4,1)) returns the code of the 4th character, 160 here. D2 has 9 characters, the 10 of A2 minus the trailing space.
CLEAN: remove line breaks and other hidden characters
CLEAN removes the non-printing characters with codes 0 to 31: line breaks (10), carriage returns (13) and tabs (9). It does not touch spaces, so the usual pair is =TRIM(CLEAN(A2)):
| A | B | C | |
|---|---|---|---|
| 1 | Imported | CLEAN | TRIM(CLEAN) |
| 2 | Ana Silva | AnaSilva | AnaSilva |
CLEAN deletes the line break outright, so the two names run together as AnaSilva . When a line break separates words, replace it with a space instead, =TRIM(SUBSTITUTE(A2,CHAR(10)," ")); the line break page shows that case.
Replace the original column with trimmed text
TRIM writes its result in another cell. To fix the data itself:
- Put
=TRIM(A2)in an empty column next to the data and fill it down. - Copy that column (Ctrl+C, Cmd+C on a Mac).
- Select the original column and use Home > Paste > Paste Values, so the cells get text, not formulas.
- Delete the helper column.
Find and Replace can remove spaces without a formula, but it cannot tell leading spaces from the ones between words: replacing a space with nothing joins Ana Silva into AnaSilva. Replacing two spaces with one, repeated until Excel finds nothing to replace, is the manual version of TRIM's inner-space rule. A single space at the start or end stays.
Frequently Asked Questions
How do I remove spaces in Excel?
=TRIM(A2) removes leading and trailing spaces and leaves one space between words. To remove every space, including those between words, use =SUBSTITUTE(A2," ","").
Why is TRIM not removing spaces?
The spaces are probably non-breaking spaces (character 160), which text copied from web pages often contains. TRIM only removes the normal space, character 32. Convert them first: =TRIM(SUBSTITUTE(A2,CHAR(160)," ")).
What is the difference between TRIM and CLEAN?
TRIM removes extra spaces. CLEAN removes non-printing characters with codes 0 to 31, such as line breaks and tabs. =TRIM(CLEAN(A2)) does both.
How do I replace the original column with the trimmed text?
Write =TRIM(A2) in a spare column and fill it down, copy that column, select the original and use Home > Paste > Paste Values. Then delete the helper column.
