How to Remove Extra Spaces in Excel (TRIM and Other Fixes)
A VLOOKUP returns #N/A even though you can see the name in the list. Two identical-looking entries won't match. Your sort puts some names in the wrong place. Nine times out of ten, the culprit is an invisible extra space.
Spot the problem
Spaces at the end of a cell are invisible. To check, use:
=LEN(A2)
This counts the characters in the cell. If "Smith" returns 6 instead of 5, there's a hidden space.
1. Use TRIM
TRIM removes:
• all spaces at the start of the text,
• all spaces at the end,
• and turns double spaces in the middle into single spaces.
1. Insert an empty column next to your data.
2. In the first row type =TRIM(A2).
3. Double-click the fill handle to copy it down.
• all spaces at the end,
• and turns double spaces in the middle into single spaces.
1. Insert an empty column next to your data.
2. In the first row type =TRIM(A2).
3. Double-click the fill handle to copy it down.
2. Replace the original data
The cleaned text is in a formula column that depends on the original. To swap it in:
1. Select the TRIM results and press Ctrl + C.
2. Click the first cell of the original column.
3. Right-click and choose Paste Options > Values (the clipboard showing 123).
4. Delete the helper column.
2. Click the first cell of the original column.
3. Right-click and choose Paste Options > Values (the clipboard showing 123).
4. Delete the helper column.
TRIM didn't work? Try this
Data copied from websites often contains non-breaking spaces. They look like normal spaces, but TRIM ignores them. Replace them with normal spaces first:
=TRIM(SUBSTITUTE(A2, CHAR(160), " "))
CHAR(160) is the non-breaking space. This formula swaps them for regular spaces, then trims.
Remove line breaks and hidden characters
Data exported from other systems can include line breaks and other invisible characters. CLEAN removes them:
=TRIM(CLEAN(A2))
This combination handles most messy imports.
Without formulas: Find and Replace
To turn double spaces into single ones across a sheet:
1. Select the column (or press Ctrl + A for the whole sheet).
2. Press Ctrl + H.
3. In Find what, type two spaces.
4. In Replace with, type one space.
5. Click Replace All. Repeat until Excel says it found nothing to replace.
2. Press Ctrl + H.
3. In Find what, type two spaces.
4. In Replace with, type one space.
5. Click Replace All. Repeat until Excel says it found nothing to replace.
This won't remove a single space at the end of a cell, though. TRIM is the better tool for that.
To remove every space, like in phone numbers or postcodes you want joined up, type one space in Find what, leave Replace with empty and click Replace All.
Microsoft 365: Clean up with one click
In recent versions of Microsoft 365, you may see Data > Data Cleanup (or a Clean Data suggestion). It spots extra spaces and inconsistent text and offers to fix them for you. It's worth a look if you have it, but TRIM works in every version.
Troubleshooting
Numbers still won't add up after TRIM. TRIM returns text. Wrap it in VALUE, =VALUE(TRIM(A2)), or use Convert to Number after pasting as values.
The formula column shows #REF! after deleting the original. You deleted the source before pasting values. Undo, then paste as values first.
Spaces between words disappeared. You used Find and Replace with a single space and nothing in Replace with. Undo with Ctrl + Z.
Wrapping up
When lookups fail for no obvious reason, check with =LEN(A2), clean up with =TRIM(A2), and use SUBSTITUTE(A2, CHAR(160), " ") for stubborn web data. Then paste as values and your data is clean.
