How to remove extra spaces in Excel
To remove extra spaces in Excel, use the TRIM function: =TRIM(A2) deletes spaces at the start and end of the text and reduces every run of spaces inside it to a single space. It is the first step of cleaning any imported list, because stray spaces make lookups, counts and duplicate checks fail.
This guide covers TRIM, deleting all spaces, the non-breaking space that TRIM misses, and how to replace the formulas with clean values.
1. TRIM removes the extra spaces
TRIM keeps one space between words and removes all the others. It does not change the original cell, so put the formula in a helper column next to the data.
Example: A name with extra spaces
| A | B | |
|---|---|---|
| 1 | Raw | Clean |
| 2 | Ana Cohen | Ana Cohen |
The two spaces at the start, the three in the middle and the one at the end are replaced by a single space between the words.
2. See how many spaces you removed
Compare the length of the original with the length of the cleaned text. A result of 0 means there was nothing to remove, which makes this a quick check on a whole column. Use LEN for both lengths.
Example: Count the removed spaces
| A | B | |
|---|---|---|
| 1 | Raw | Removed |
| 2 | Ana Cohen | 5 |
The original has 14 characters and the trimmed text has 9, so 5 spaces were removed.
3. Delete every space
Sometimes no space should remain, as in a product code or a phone number. Use SUBSTITUTE to replace each space with nothing.
Example: Remove all spaces
| A | B | |
|---|---|---|
| 1 | Raw | No spaces |
| 2 | Ana Cohen | AnaCohen |
Every space, including the ones inside the text, is replaced with an empty string.
4. The space that TRIM cannot see
Text copied from a web page often contains a non-breaking space, character code 160. It looks like a space but TRIM leaves it alone. Replace it first with =TRIM(SUBSTITUTE(A2,CHAR(160)," ")). Other invisible characters, such as line breaks, can be removed with CLEAN. When data still behaves strangely, check the length of a cell with LEN to find out whether it holds hidden characters.
5. Replace the formulas with clean values
Copy the helper column, select the original column and choose Paste Special > Values, then delete the helper column. For a quick fix on a column without formulas, Find and Replace can replace a double space with a single space, and you can repeat it until no replacement is found.
Common mistakes
TRIM seems to do nothing. The spaces are non-breaking spaces from a web page.
Fix: Substitute CHAR(160) with a normal space first, then apply TRIM.
Cleaning the data but keeping the old column in the lookup. The formulas still compare against the dirty text.
Fix: Point the lookup at the cleaned column, or paste the clean values over the original.
Trimming numbers stored as text. TRIM returns text, so a trimmed number is still text.
Fix: Multiply the result by 1, or use VALUE, to turn it into a real number.
Frequently asked questions
How do I remove spaces only at the start?
TRIM cannot do that alone because it also reduces the spaces inside. Use a formula that finds the first non-space character, or accept the single spaces between words that TRIM leaves.
Can I fix a whole column at once?
Yes. Fill the formula down the helper column. In Microsoft 365 you can also write =TRIM(A2:A100) once and let it spill.
Why does my lookup fail even after TRIM?
Check for a non-breaking space, a different capitalization that your lookup treats as different, or a number stored as text in one of the two lists.
Related functions
- TRIMTRIM removes extra spaces from text: at the start, at the end, and doubles between words.
- SUBSTITUTESUBSTITUTE replaces specific text inside a cell with other text, everywhere or only one occurrence.
- LENLEN returns the number of characters in a text, spaces included.
- PROPERPROPER capitalizes the first letter of each word, UPPER makes text all caps, LOWER all small letters.
More on cleaning text data
The guide shows TRIM, SUBSTITUTE and the non-breaking space. This piece adds how to find out whether spaces are the problem, a habit for cleaning imported data, and how to stop spaces from entering the sheet in the first place.
Find out whether spaces are the problem
Spaces cannot be seen, so do not guess. Two quick tests tell you. First, put =LEN(A2) next to the cell and compare it with the number of letters you can see. Second, compare the original and the cleaned text with a formula that returns TRUE or FALSE. If the answer is FALSE on some rows, those rows had extra characters.
=A2=TRIM(A2)
=LEN(A2)-LEN(TRIM(A2))A habit for imported data
Never clean the original column in place. Add a helper column with the cleaning formula, check it, and only then paste the values over the original. Clean in this order: replace non-breaking spaces, trim, then fix the capitalization with PROPER if you need it. Doing the trim last can leave a space that the earlier step created.
=PROPER(TRIM(A2))Stop spaces at the door
Data validation can reject entries that start or end with a space. Choose Custom and use a rule that compares the cell with its trimmed version. When a form or a person types the data, this saves the cleaning step for every later user of the sheet.
Spaces are also the reason that lookups return #N/A even though the value is plainly there. Read [how to fix #N/A and other errors](/guides/fix-n-a-and-other-errors-in-excel) and practice in [the data cleaning lesson](/learn/data-cleaning-tools).