TRIM Function in Google Sheets

Use TRIM to strip extra spaces from text — leading, trailing and double spaces between words — so lookups and matches finally work.

=TRIM(A2)

Removes spaces before, after and any doubled spaces between words in A2, leaving single spaces.

How it works

TRIM removes leading and trailing spaces and collapses any run of multiple spaces inside the text down to a single space. It is the go-to fix when a VLOOKUP or exact match fails for no visible reason — the culprit is almost always an invisible trailing space. TRIM does not remove non-breaking spaces or line breaks; pair it with CLEAN or SUBSTITUTE for those.

Variations

Also remove non-printing characters

=TRIM(CLEAN(A2))

CLEAN strips line breaks and control characters that TRIM leaves behind.

Remove non-breaking spaces too

=TRIM(SUBSTITUTE(A2, CHAR(160), " "))

CHAR(160) is the non-breaking space common in copied web text.

Clean a whole column at once

=ARRAYFORMULA(TRIM(A2:A100))

Returns a cleaned copy of the range.

Examples

Scenario	Formula
Fix a lookup key before matching	`=VLOOKUP(TRIM(A2), D:E, 2, FALSE)`
Standardise pasted names	`=TRIM(CLEAN(A2))`

FAQ

Why does my VLOOKUP fail until I use TRIM?

The lookup value or the source has an extra space, so the texts do not match exactly. Wrapping both in TRIM removes the mismatch.

Does TRIM remove all spaces?

No. It keeps single spaces between words and only removes extras. To delete every space use SUBSTITUTE(A2," ","").

TRIM did not fix my spaces — why?

They are probably non-breaking spaces (CHAR 160). Replace them first: =TRIM(SUBSTITUTE(A2,CHAR(160)," ")).

Related formulas

→ See all 34 Google Sheets formulas in one cheat sheet