To remove extra spaces in Excel, put =TRIM(A2) in a helper column, then paste the results back as values. If TRIM seems to do nothing, the stray character usually isn't a normal space. This guide shows how to check for that with LEN and when to add CLEAN.
Step 1: Measure the cell with LEN before you fix anything
Invisible characters all look the same on screen, so count them first. In an empty column next to your data, enter:
=LEN(A2)-LEN(TRIM(A2))
Read the result like this:
- A number above 0: TRIM found ordinary spaces to remove. Go to Step 2.
- 0, but the text still looks padded or lookups still fail: the extra characters are something TRIM doesn't touch. Go to Step 3.
This check matters because pasted or exported data often carries stray characters that quietly break VLOOKUP, COUNTIF and duplicate checks. Two text values that look identical won't match if one has a hidden character.
Step 2: Remove leading, trailing and double spaces with TRIM
The basic formula, as How-To Geek shows, is:
=TRIM(A2)
According to Exceljet, TRIM leaves a single space between words and no leading or trailing spaces. So " Jane Smith " becomes "Jane Smith".
- Enter
=TRIM(A2)in the first row of an empty column. - Fill it down to the last row of your data.
- Select the formula cells and copy them.
- Select the original column and use Paste Special to paste as values.
- Delete the helper column.
If you skip step 4 and delete the helper column, your cleaned text disappears with it, because it was only ever a formula result.
Step 3: Why TRIM isn't working on some cells
TRIM only removes one character: the ASCII space, character 32. Both Exceljet and How-To Geek say this. Anything else that looks like blank space, such as a tab, a line break or a non-breaking space, passes straight through.
That is the usual reason people search for "Excel TRIM function not working". The formula works fine. The character just isn't the one it removes.
Remove tabs and line breaks with CLEAN
Microsoft's CLEAN page says the function "removes all nonprintable characters from text" and is meant for text imported from other applications. It removes the first 32 nonprinting characters of 7-bit ASCII, values 0 to 31. Tabs and line feeds fall in that range.
Microsoft's own example: if A2 contains
=CHAR(9)&"Monthly report"&CHAR(10)
then
=CLEAN(A2)
returns "Monthly report", with the tab (9) and line feed (10) gone.
Combine TRIM and CLEAN for imported data
Each function misses what the other handles. TRIM ignores characters 0 to 31, and CLEAN ignores ordinary spaces. Exceljet notes you can nest them to handle both:
=TRIM(CLEAN(A2))
Paste the results back as values, the same way as in Step 2. Then run the LEN check from Step 1 again on the cleaned column to confirm nothing is left.
What CLEAN still won't remove
CLEAN has limits too. Microsoft says the Unicode character set has more nonprinting characters, with values 127, 129, 141, 143, 144 and 157, and CLEAN does not remove these on its own.
The non-breaking space, character 160, isn't in either function's list either. TRIM only handles 32, and CLEAN only handles 0 to 31, so neither formula in this guide will strip it.
Excel versions Microsoft lists for CLEAN
Microsoft's CLEAN page lists Excel for Microsoft 365 on Windows and Mac, plus Excel 2024, 2021, 2019 and 2016.
Once your text is clean, tools that compare values, such as UNIQUE for listing distinct entries, will stop treating "Jane Smith" and "Jane Smith " as two different people.
