TRIM is the first thing people reach for to remove extra spaces, and it quietly fails on some of the most common culprits. This page covers what TRIM does, the invisible characters it misses (non-breaking spaces, line breaks, tabs), how to find out which one you have, and formula fixes for each, including a whole column at once.
Row 1 holds headers. Build the test inputs with formulas so you can see the problem:
A2: =" John Smith " (leading, trailing and doubled spaces)
A3: ="John"&CHAR(160)&"Smith" (a non-breaking space)
A4: ="John"&CHAR(10)&"Smith" (a line break)
A5: ="John"&CHAR(9)&"Smith" (a tab)
=TRIM(A2)
This returns John Smith. TRIM removes leading and trailing spaces and collapses runs of spaces between words into one. It only touches the normal space character, code 32.
Text pasted from web pages, PDFs and some exports contains non-breaking spaces. They look identical to spaces but are a different character, so TRIM leaves them alone. On A3, =TRIM(A3) still returns John and Smith joined by the odd space. Swap them for normal spaces first, then TRIM:
=TRIM(SUBSTITUTE(A3,CHAR(160)," "))
This returns John Smith with a normal space.
A line break is CHAR(10) and a tab is CHAR(9). CLEAN strips non-printing characters, which includes both, but it deletes them rather than replacing them with a space, so "John" and "Smith" would run together as JohnSmith. If you want a space in their place, use SUBSTITUTE:
=TRIM(SUBSTITUTE(SUBSTITUTE(A4,CHAR(10)," "),CHAR(9)," "))
If you just want them gone, =CLEAN(A4) does it. You can combine everything:
=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,CHAR(160)," "),CHAR(10)," "),CHAR(9)," "))
REGEXREPLACE can replace any run of whitespace with a single space. In regular expressions, \s matches spaces, tabs and line breaks, but not the non-breaking space, so add it explicitly:
=TRIM(REGEXREPLACE(A2,"[\s\x{00A0}]+"," "))
This handles all four sample cells and returns John Smith each time (after TRIM removes the end spaces).
Select the range, then choose Data > Data cleanup > Trim whitespace. It edits the cells in place, so work on a copy of the tab first. If your data has non-breaking spaces, test it on a few rows to check it catches them before you rely on it. The formulas above leave the original untouched.
First, compare lengths:
=LEN(A3)-LEN(TRIM(A3))
If this is above 0, TRIM found ordinary spaces to remove. If a cell looks messy but this gives 0, the problem is not a normal space.
Then inspect the characters one at a time. To see the code of the fifth character:
=CODE(MID(A3,5,1))
For A3, this returns 160. 32 is a normal space, 10 a line break, 9 a tab. To list every character code in a cell at once:
=ARRAYFORMULA(CODE(MID(A3,SEQUENCE(LEN(A3)),1)))
This returns a column of codes, and the odd one out is your culprit.
Put one formula in B2 instead of filling down:
=ARRAYFORMULA(IF(A2:A="","",TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2:A,CHAR(160)," "),CHAR(10)," "),CHAR(9)," "))))
The IF keeps empty rows empty. If you want to replace the original values, copy column B and use Edit > Paste special > Values only over column A, then delete B.
Two values that look the same but differ by an extra invisible character are different to Sheets. That is the most common reason a VLOOKUP, MATCH or COUNTIF reports #N/A or a count of zero for a value you can see. Clean both the search value and the list, then look up again. The full set of causes is at https://www.snakbacks.com/vlookup-na-error-google-sheets
One surprise from testing: when comparing, Sheets treats a non-breaking space like an ordinary space. "John" + CHAR(160) + "Smith" does match "John Smith" in VLOOKUP and in a plain = comparison, even though TRIM leaves the character in place. It breaks a lookup when it is an extra space, for example a trailing one: "Initech" + CHAR(160) will not match "Initech", and TRIM will not fix it. So do not use =A2="John Smith" to prove a cell is clean; check LEN or CODE as shown above.
Other invisible characters exist, such as zero-width spaces (code 8203). CODE shows them, and you can remove one with SUBSTITUTE(A2,CHAR(8203),""). If the code you find is not one of the above, search for that code number and substitute it the same way.
Disclosure: Snakbacks, the publisher of this site, sells a paid script pack and makes two free add-ons for Google Sheets.
Everything above works with formulas alone. If you clean data like this often, the Data Cleanup Kit for Google Sheets (US$15, no add-on install): https://snakback.gumroad.com/l/data-cleanup-kit includes a ready-made clean-text tab that trims including non-breaking spaces and builds a match key, so you paste data in and read clean values out.
Our two free add-ons, Fuzzy Match and Duplicate Remover, are coming soon to the Google Workspace Marketplace.
Snakbacks publishes the Duplicate Remover and Fuzzy Match add-ons.