Remove Spaces in Excel, Including the Ones TRIM Misses
By Mark Fulton · 2026-10-09 · 10 min read
Use =TRIM(A2) to remove leading, trailing and doubled spaces, and =SUBSTITUTE(A2," ","") to remove every space, including the ones between words. If the result still looks wrong, the cell is not holding a plain space. Text copied from web pages and PDFs usually carries a non-breaking space (character code 160), which TRIM ignores by design, and sometimes line breaks, tabs or zero-width characters that need their own fix. The reliable method is to test first: compare LEN before and after, read the character's code with UNICODE, then pick the removal that matches. Once you know which character you have, the fix is one formula or one Find and Replace.
Most tutorials stop at TRIM, and TRIM works on the data the tutorial author typed. It fails on the data you were sent.
Which kind of space is in your cell?
Start with a column that should match but doesn't. Say A2 contains a customer name and a lookup against it returns #N/A even though the name is right there in the other list. Two checks tell you what you are dealing with.
Check 1: does TRIM change the length? In a spare cell, enter:
=LEN(A2)-LEN(TRIM(A2))
A result of 0 means TRIM found nothing to remove. A positive number is how many plain spaces it can remove. If you can see a gap in the text and this returns 0, you have a character TRIM does not recognise.
Check 2: what is the first or last character, exactly?
=UNICODE(LEFT(A2,1))
=UNICODE(RIGHT(A2,1))
A plain space returns 32. Anything else tells you what it is. Run the same formula on a character in the middle with UNICODE(MID(A2,5,1)), changing 5 to the position of the gap. You can also test for a specific culprit directly:
=ISNUMBER(FIND(UNICHAR(160),A2))
TRUE means a non-breaking space is in there. To count the problem cells across a whole column at once, use this and adjust the range:
=SUMPRODUCT(--(LEN(A2:A500)<>LEN(TRIM(A2:A500))))
That counts cells TRIM would change. It will not count cells whose only problem is a character TRIM cannot see, which is why Check 2 matters.
The five characters that look like spaces
| Character | What it is | Code | Does TRIM remove it? | Formula that does | Find what box |
|---|---|---|---|---|---|
| Space | The ordinary spacebar character | 32 | Yes, except single spaces between words | =TRIM(A2) or =SUBSTITUTE(A2," ","") |
One space |
| Non-breaking space | Web pages and PDFs use it to stop a line wrapping | 160 | No | =SUBSTITUTE(A2,UNICHAR(160)," ") |
Hold Alt and type 0160 on the numeric keypad (Windows), or copy the character out of a cell |
| Tab | Pasted from tables and some exports | 9 | No | =CLEAN(A2), or =SUBSTITUTE(A2,CHAR(9)," ") to keep the word gap |
Copy the tab out of a cell and paste it |
| Line break | Alt+Enter inside a cell, or from a multi-line field | 10 | No | =CLEAN(A2), or =SUBSTITUTE(A2,CHAR(10)," ") to keep the word gap |
Ctrl+J (Windows) |
| Zero-width space | Invisible, no width at all, common in copied web text | 8203 | No | =SUBSTITUTE(A2,UNICHAR(8203),"") |
Copy it out of a cell |
Read the Code column as the answer to Check 2. If UNICODE returns 160, jump to the non-breaking space section. If it returns 9 or 10, CLEAN is your function. If it returns 8203 you are looking at a character that nothing built into Excel's cleaning functions touches, and SUBSTITUTE is the only formula route.
Why doesn't TRIM remove every space?
Because TRIM was written for one character and says so. Microsoft's TRIM documentation describes it as removing the 7-bit ASCII space character, value 32, and notes that the Unicode character set has an additional space, the non-breaking space with a decimal value of 160, which TRIM does not remove. It is the character behind the HTML entity , which is why it travels with anything copied from a web page.
TRIM also does a second thing people forget: it leaves single spaces between words alone. That is correct behaviour for names and addresses, and wrong if you wanted New York to become NewYork.
CLEAN has its own limit. CLEAN removes nonprintable characters, the low-numbered control codes that arrive with imported text, which includes the tab (9) and line feed (10). It does not touch code 160 or code 8203, and because it deletes rather than replaces, CLEAN("one" & CHAR(10) & "two") gives onetwo. If the line break was separating words, swap it for a space first.
So each function covers one slice:
- TRIM: plain spaces, and only the extra ones.
- CLEAN: control characters, including tabs and line breaks.
- SUBSTITUTE: any single character you name, which makes it the only tool that covers 160 and 8203.
How do you remove non-breaking spaces?
Replace the character with a normal space first, then let TRIM tidy up the rest. The order matters: if you simply delete code 160, Smith John becomes SmithJohn, and if you leave it alone TRIM never sees it.
=TRIM(SUBSTITUTE(A2,UNICHAR(160)," "))
For a cell that might hold any of the four problem characters from the table, nest them in a single formula:
=TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,UNICHAR(160)," "),CHAR(10)," "),CHAR(9)," "),UNICHAR(8203),"")))
Read it from the inside out. Non-breaking spaces become plain spaces, line feeds and tabs become plain spaces, zero-width spaces disappear, CLEAN removes anything else nonprintable, and TRIM collapses the leftover runs of spaces. SUBSTITUTE replaces every occurrence of the old text with the new text by default, which is what makes this chain work.
Fill the formula down the helper column, check a few rows against the originals, and you have clean text. UNICHAR and UNICODE need Excel 2013 or later. On older versions use CHAR(160) and CODE, which work on Windows for this particular character.
How do you strip spaces in place without a helper column?
Formulas always write to somewhere else. If you need the original cells cleaned, there are three routes, and each has a catch.
Find and Replace (Ctrl+H). Put the character in Find what, leave Replace with empty or type a single space, and choose Replace All. It is fast and it works in place. The catch is that it does exactly one character in one pass. It will remove all non-breaking spaces or all plain spaces, but it cannot do what TRIM does (collapse a run of spaces down to one while keeping the gap between words). To get a non-breaking space into the Find box on Windows, hold Alt and type 0160 on the numeric keypad, or copy one out of an affected cell and paste it. Select the column first, or Replace All will touch the whole sheet.
Formula, then paste values over the original. Build the helper column, copy it, select the original column, and use Paste Special > Values. Then delete the helper. This is the dependable way to get TRIM's collapsing behaviour in place, and it leaves no formulas behind. Do it on a copy of the sheet, because pasting values cannot be undone once the file is saved.
Power Query. Load the range through Data > From Table/Range, select the column, and use the Transform tab's Format menu, which has Trim and Clean. This is the right route when the same messy export arrives every month, because the steps rerun on refresh. Note that Text.Trim in Power Query trims whitespace characters by default rather than only the plain space, so it behaves differently from the worksheet TRIM. Check a sample row rather than assuming. Menu names move between versions and between Windows, Mac and Excel for the web, so look for the Data tab if Transform Data sits elsewhere.
Flash Fill (Ctrl+E) gets suggested for this job and I would skip it. It writes to an adjacent column rather than in place, and it works by guessing a pattern from your examples, which is the wrong tool for a character you cannot see.
What about spaces inside numbers?
A number such as 1 234 or 12 500 that arrived with a thousands space is text, so it will not sum, sort or format as a number. The space is usually a plain 32, but spreadsheets exported from European systems and some PDF extractions use the non-breaking one, so test it with the same UNICODE check.
For a single column, the formula route is:
=VALUE(SUBSTITUTE(SUBSTITUTE(A2,UNICHAR(160),"")," ",""))
Both space types are removed, and VALUE turns the remaining digits into a real number. Alternatively, select the column, open Find and Replace, remove the space character, and Replace All. Cells that contain only digits afterwards normally flip to real numbers, which you can confirm by checking they right-align or that the status bar shows a Sum when you select them. If a decimal comma or a currency symbol is also involved, the problem is wider than spaces, and why Excel's SUM isn't working covers the rest of the numbers-as-text family.
How does Google Sheets handle it?
The same functions exist with the same names. TRIM, SUBSTITUTE, CLEAN and UNICODE all work in Sheets, and =TRIM(SUBSTITUTE(A2,CHAR(160)," ")) is the usual pattern for a non-breaking space. Sheets also has an in-place option, Data > Data cleanup > Trim whitespace, which cleans the selected range without a helper column. As with Excel, run the UNICODE test on one cell if a gap survives the menu command, because a menu that trims "whitespace" is not a promise about every invisible character. Menu paths shift between releases, so check the Data menu if you do not see it.
Why does this matter before you do anything else?
Because stray spaces are the cause behind two of the most frustrating spreadsheet bugs. A trailing space makes "North " a different value from "North", so exact-match lookups return #N/A, and pivot tables show the same label twice. The same thing hides duplicates: two rows that look identical do not match because one carries a space. Clean the text first, then run remove duplicates safely or highlight duplicates in Excel, and add this step to your pre-send checklist so it stops being a surprise.
Frequently asked questions
Why is TRIM not working in Excel?
Almost always because the space is not character 32. The most common cause is a non-breaking space (160) from web or PDF text, and TRIM is documented to leave it alone. Run =UNICODE(RIGHT(A2,1)) on a cell that looks affected. If it returns anything other than 32, use =TRIM(SUBSTITUTE(A2,UNICHAR(160)," ")). The other possibility is that the cell is fine and the problem is a number stored as text, which TRIM cannot help with.
How do I remove spaces between numbers?
Use =VALUE(SUBSTITUTE(SUBSTITUTE(A2,UNICHAR(160),"")," ","")) to remove both plain and non-breaking spaces and convert the result to a number. Or select the column and use Find and Replace (Ctrl+H) to replace the space character with nothing. Afterwards, confirm the cells are real numbers by checking that they right-align.
Does TRIM remove line breaks?
No. A line break inside a cell is character 10, and TRIM only handles character 32. Use =CLEAN(A2) to delete it, or =SUBSTITUTE(A2,CHAR(10)," ") to turn it into a space so the words either side do not run together. If the data has many such cells, wrap TRIM around the SUBSTITUTE as well.
How do I find cells with trailing spaces?
Compare lengths: =LEN(A2)<>LEN(TRIM(A2)) returns TRUE for any cell TRIM would change. Fill it down and filter for TRUE, or use it as a conditional formatting rule to highlight affected cells. To see which character is at the end, use =UNICODE(RIGHT(A2,1)). A result of 32 means a plain trailing space, and 160 means a non-breaking one.
Find the damage before you fix it
Fixing one column by formula is fine. Knowing how many cells across the whole sheet carry stray spaces, before you decide where to look, is faster. Drop the file on the spreadsheet cleaner. It counts the cells with leading or trailing spaces and trims them only when you approve. Nothing uploads, parsing and the download happen in your browser, and the cleaner reports a total for the sheet rather than a column-by-column breakdown, so use the formulas above to locate the worst columns. It trims the ends of cell text; it does not collapse or remove spaces between words, so that part is still a TRIM or Find and Replace job.