How to Remove Extra Spaces in Google Sheets
Type =TRIM(A2) to strip leading, trailing and double spaces from text — messy imported data becomes clean in one step.
=TRIM(A2)
Removes spaces at the start and end and collapses runs of spaces inside the text to a single space.
How it works
TRIM fixes the most common text mess: stray spaces from copy-paste and imports. It removes leading and trailing spaces entirely and reduces any internal run of spaces to one. It does not remove non-breaking spaces (character 160) that often come from the web, or line breaks — for those, combine TRIM with SUBSTITUTE and CLEAN. To clean a whole column at once, wrap TRIM in ARRAYFORMULA.
Variations
Remove line breaks too
=CLEAN(TRIM(A2))
CLEAN strips non-printing characters like line breaks; TRIM handles the spaces.
Remove non-breaking spaces from web data
=TRIM(SUBSTITUTE(A2,CHAR(160)," "))
Swaps char 160 for a normal space first, then trims.
Clean an entire column at once
=ARRAYFORMULA(TRIM(A2:A))
One formula that trims every row below.
Examples
| Scenario | Formula |
|---|---|
| Clean imported names | =TRIM(A2) |
| Fix web-copied text with odd spaces | =TRIM(SUBSTITUTE(A2,CHAR(160)," ")) |
FAQ
Why does TRIM leave some spaces behind?
They are probably non-breaking spaces (CHAR(160)) from a web copy. Replace them first: =TRIM(SUBSTITUTE(A2,CHAR(160)," ")).
How do I remove all spaces, not just extra ones?
Use SUBSTITUTE to delete every space: =SUBSTITUTE(A2," ",""). TRIM only removes leading, trailing and duplicate spaces.
How do I trim a whole column?
Wrap it in ARRAYFORMULA: =ARRAYFORMULA(TRIM(A2:A)). It cleans every row from a single cell.