TRIM Not Working in Excel? Non-Breaking Spaces Explained
By ExcelReady TeamUpdated 6 min read
The short answer
TRIM only removes the normal space character, code 32. When TRIM is not working, the cell usually holds a non-breaking space (code 160) copied from a web page, or a line break or tab. Swap it for a normal space first, then trim: =TRIM(SUBSTITUTE(A2,CHAR(160)," ")). Add CLEAN for line breaks and tabs.
Key takeaways
- TRIM removes only the normal space, character 32. Non-breaking spaces (160), tabs (9) and line breaks (10) survive it.
- Compare LEN(A2) with LEN(TRIM(A2)) and check the last character with CODE(RIGHT(A2)) to see what is really there.
- =TRIM(SUBSTITUTE(A2,CHAR(160)," ")) fixes the most common case: spaces copied from web pages.
- TRIM keeps one space between words by design. To remove every space, use SUBSTITUTE(A2," ","").
- TRIM always returns text, so wrap trimmed numbers in VALUE before you add or look them up.
On this page+
When TRIM is not working in Excel, the problem is almost never TRIM. It removes one kind of space, the normal space typed with the space bar, and your cell holds something that only looks like a space. Most often that is a non-breaking space copied from a web page. This guide shows how to find the hidden character and remove it, plus four other reasons TRIM seems to do nothing.
What TRIM actually removes#
TRIM removes normal spaces at the start and end of a cell and turns runs of spaces between words into a single space. Its syntax has one argument:
=TRIM(text)So =TRIM(" Lena Park ") returns "Lena Park". Every character on a computer has a number, called its character code. The normal space is code 32, and that is the only character TRIM touches. These look empty too, but TRIM leaves them alone:
| Character | Code | Where it comes from | Removed by TRIM? |
|---|---|---|---|
| Normal space | 32 | The space bar | Yes |
| Non-breaking space | 160 | Web pages, some exports | No |
| Line break | 10 | Alt+Enter in a cell, exported notes | No |
| Tab | 9 | Text files, copied tables | No |
The TRIM function guide has more examples of what it does to normal spaces.
How to find out what's really in the cell#
Check the length and the codes before you guess. Say A2 holds "Lena Park" with two invisible characters at the end.
| Check | Formula | What it tells you |
|---|---|---|
| Total characters | =LEN(A2) | Counts every character, including hidden ones |
| Characters after TRIM | =LEN(TRIM(A2)) | If this equals LEN(A2), TRIM removed nothing |
| Code of the last character | =CODE(RIGHT(A2)) | 32 is a space, 160 a non-breaking space, 10 a line break, 9 a tab |
| Code of the first character | =CODE(LEFT(A2)) | The same check at the start of the cell |
If LEN gives 11 for "Lena Park", which has only 9 visible characters, and LEN of the trimmed text is still 11, TRIM found no normal spaces to remove. If CODE then returns 160, you've found your culprit.
For unusual characters above 255, CODE can give a misleading answer. Use =UNICODE(RIGHT(A2)) instead, which works in Excel 2013 and later.
Fix 1: Non-breaking spaces from web pages#
Replace each non-breaking space with a normal space, then let TRIM clean up:
=TRIM(SUBSTITUTE(A2,CHAR(160)," "))Read it from the inside out. CHAR(160) produces the non-breaking space. SUBSTITUTE swaps every one of them for a normal space. TRIM then removes the spaces at the ends and doubles in the middle. For "Lena" + non-breaking space + "Park" + two non-breaking spaces, the result is Lena Park. The SUBSTITUTE function guide explains its arguments.
On a Mac, CHAR follows the Mac character set, so if CHAR(160) doesn't catch the space, use UNICHAR(160) instead. UNICHAR always means the same Unicode character on every platform.
You can also fix the cells in place with Find and Replace (Windows: Ctrl+H; Mac: ⌃H). On Windows, click in the Find box, hold Alt and type 0160 on the number pad, then replace with a single space. Run TRIM afterwards for any doubles.
Fix 2: Line breaks and tabs#
CLEAN removes non-printing characters with codes 0 to 31, which includes line breaks (10) and tabs (9). Combine all three for messy imports:
=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))One catch: CLEAN deletes a line break, so "Lena" + line break + "Park" becomes "LenaPark". When a line break separates words, swap it for a space instead: =TRIM(SUBSTITUTE(A2,CHAR(10)," ")). The comparison of TRIM vs CLEAN covers the difference in detail.
Some web text also contains a zero-width space, Unicode 8203, which has no width at all. Remove it with =SUBSTITUTE(A2,UNICHAR(8203),"").
Fix 3: You expected TRIM to remove every space#
TRIM keeps one space between words on purpose. If you need "AB 12 C" to become "AB12C", for example in product codes or phone numbers, TRIM is the wrong tool. Use SUBSTITUTE to replace every space with nothing:
=SUBSTITUTE(A2," ","")The guide on how to remove spaces in Excel compares TRIM, SUBSTITUTE and Find and Replace for each kind of space.
Fix 4: TRIM worked, but your formulas still fail#
Sometimes TRIM did its job and something else is wrong. Check these two cases:
- The formula still reads the original column. TRIM returns a clean copy in a new cell. If your VLOOKUP or COUNTIF still points at column A, it still sees the spaces. Either point it at the trimmed column, or copy the trimmed cells and paste them over the originals as values (Windows: Ctrl+Alt+V; Mac: ⌃⌘V, then choose Values).
- TRIM turned numbers into text. TRIM always returns text, so
=TRIM(" 250")gives the text "250". SUM ignores it and a lookup against real numbers fails. Wrap it in VALUE:=VALUE(TRIM(A2))returns the number 250.
Hidden spaces are one of the most common reasons other formulas break, too. See why COUNTIF returns the wrong count and why VLOOKUP is not working for how they show up there.
A clean-up formula for messy imported text#
For a column copied from the web or a system export, this one formula handles all the usual suspects:
=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))Put it in a helper column next to the data, fill it down (Windows: Ctrl+D; Mac: ⌘D), check a few rows with LEN, and then paste the results over the original as values. Delete the helper column when you're done. For a full clean-up routine that also fixes capitals, duplicates and dates, see the guide to cleaning messy data in Excel.
Practise it#
Hidden characters are much easier to deal with once you've fixed a few yourself. Practise cleaning messy text in a live spreadsheet: you'll use TRIM, CLEAN and SUBSTITUTE on real messy names and codes, and the sheet checks every formula instantly. Start with the non-breaking space exercise on this page.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
The name in A2 was copied from a web page and has non-breaking spaces, so TRIM alone won't clean it. In B2, swap CHAR(160) for a normal space with SUBSTITUTE, then wrap the result in TRIM.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Clean name | |||
| 2 | Lena Park | |||
| 3 | Omar Haddad | |||
| 4 | Yuki Tanaka | |||
| 5 | ||||
| 6 | ||||
| 7 |
Tip: SUBSTITUTE takes the cell, the character to replace, CHAR(160), and the new text, " ". Put TRIM around the whole thing.
Practise this in a lesson
Frequently asked questions
Why is TRIM not removing spaces in Excel?
The spaces are probably not normal spaces. Data copied from web pages often contains non-breaking spaces, character 160, which TRIM ignores. Use =TRIM(SUBSTITUTE(A2,CHAR(160)," ")) to swap them for normal spaces and then trim.
How do I remove a non-breaking space in Excel?
Use SUBSTITUTE to replace CHAR(160) with a normal space, then TRIM the result. You can also use Find and Replace: in the Find box, hold Alt and type 0160 on the number pad (Windows), then replace with a space or nothing.
Why does TRIM leave a space between words?
That is how TRIM is designed. It removes spaces at the start and end and reduces runs of spaces between words to one. To remove every space, use =SUBSTITUTE(A2," ","") instead.
Why does my lookup still fail after TRIM?
Either the lookup still points at the original, untrimmed column, or TRIM turned a number into text. Paste the trimmed values over the originals, and wrap trimmed numbers in VALUE.
What is the difference between TRIM and CLEAN?
TRIM removes extra normal spaces. CLEAN removes non-printing characters with codes 0 to 31, such as line breaks and tabs. Neither removes the non-breaking space, character 160.