TRIM removes spaces at the start and end of a cell and turns runs of spaces in the middle into one. For text you typed yourself, that is enough. For text pasted from a web page, a PDF or another app, it often isn’t: the cell still won’t match, sort or add up, and TRIM appears to do nothing. The reason is that some characters look like spaces but are not the space TRIM looks for.
Tested in Google Sheets. On November 11, 2026 we built cells with each kind of hidden character, measured them with LEN before and after every fix, and kept only the formulas that removed them. How we test
Step 1: find out what is in the cell
Don’t guess. Two formulas tell you what you are dealing with:
=LEN(A2)counts characters. If “Hello world” gives more than 11, something invisible is there.=CODE(RIGHT(A2))gives the code of the last character, and=UNICODE(MID(A2, 6, 1))the code of the sixth. Move the position until you find the odd one.
| Code | Character | Does TRIM remove it? | Fix |
|---|---|---|---|
| 32 | Normal space | Yes | =TRIM(A2) |
| 160 | Non-breaking space (common in web pages) | No | =TRIM(SUBSTITUTE(A2, CHAR(160), " ")) |
| 9 | Tab | Only at the start or end | =TRIM(CLEAN(A2)) |
| 10 | Line break | No, when it is in the middle | =TRIM(CLEAN(A2)) or SUBSTITUTE with a space |
| 8203 | Zero-width space | No, and CLEAN doesn’t either | =REGEXREPLACE(A2, "\x{200B}", "") |
Normal extra spaces: TRIM
With " Hello world " (15 characters), =TRIM(A2) returned “Hello world”, 11 characters. TRIM never removes the single spaces between words.
Non-breaking spaces: SUBSTITUTE, then TRIM
A non-breaking space (code 160) is the usual reason TRIM “doesn’t work”. We tested “Hello”, a non-breaking space, “world” and another non-breaking space at the end: 12 characters before TRIM and 12 after. =EXACT(TRIM(A2), "Hello world") returned FALSE.
Replace them with normal spaces first, then trim:
=TRIM(SUBSTITUTE(A2, CHAR(160), " "))
That returned 11 characters, the correct “Hello world”.
Found in testing: the regular expression \s did not match the non-breaking space either. =REGEXREPLACE(A2, "\s", "") left the cell at 12 characters. Naming the character explicitly worked: =REGEXREPLACE(A2, "[\s\x{00A0}]+", " ") followed by TRIM gave the correct 11. The Unicode class \p{Zs}, which some guides suggest, was rejected with is not a valid regular expression.
Tabs and line breaks: CLEAN
CLEAN removes non-printing characters such as tabs (9) and line breaks (10). TRIM removed a tab at the start of a cell, but a tab or a line break between two words stayed: =LEN(TRIM("a"&CHAR(10)&"b")) returned 3. Our test cell (a tab, “Tab”, a line break, “line” and a space) went from 10 characters to 8 with TRIM alone, to 8 with CLEAN alone, and to 7 with =TRIM(CLEAN(A2)).
CLEAN deletes the line break without leaving a space, so “line1” and “line2” become “line1line2”. If you want a space between them, use =TRIM(SUBSTITUTE(A2, CHAR(10), " ")) instead.
Zero-width spaces: REGEXREPLACE
A zero-width space (code 8203) is invisible and takes up no room, so the cell looks perfect. In our test, “Zero”, a zero-width space and “width” measured 10 characters, and neither TRIM nor CLEAN changed that. This removed it:
=REGEXREPLACE(A2, "\x{200B}", "")
One formula for pasted text
When you don’t know what came in with the paste, this combines the fixes above. It turns tabs, line breaks and non-breaking spaces into normal spaces, deletes zero-width spaces and trims the result:
=TRIM(REGEXREPLACE(REGEXREPLACE(A2, "\x{200B}", ""), "[\s\x{00A0}]+", " "))
We ran it on every test cell. The extra normal spaces and the non-breaking spaces both came out as “Hello world” (11 characters), the tab and line break cell became “Tab line”, the zero-width space was removed (9 characters) and “line1”, a line break and “line2” became “line1 line2”. Check your own data with LEN before and after.
What about Data > Data cleanup > Trim whitespace?
Google Sheets also has a menu command that trims cells in place, without a helper column. We ran the same operation on a cell containing two normal spaces, “Hi”, a non-breaking space, “there”, a non-breaking space and a normal space. It removed the normal spaces at both ends, but the cell still ended with the non-breaking space: 9 characters, and the last one was code 160. The command has the same blind spot as the TRIM function.
Replace the original column
- Put the formula in an empty column next to the data and fill it down.
- Copy the new column and use Edit > Paste special > Values only over the original.
- Delete the helper column.
If the cleaned values are numbers, wrap the formula in VALUE. A number with a non-breaking space next to it is not a number to Google Sheets: =VALUE("5"&CHAR(160)) failed with cannot be parsed to number, while =VALUE(" 5") with a normal space worked. Our guide to converting text to numbers covers the other cases.