How to Use TRIM in Google Sheets: Comprehensive Guide

TRIM removes extra spaces from text: it deletes leading and trailing spaces and reduces any run of spaces between words to a single space. It is the first fix to try when a VLOOKUP or COUNTIF mysteriously fails on data that looks identical, because invisible spaces are one of the most common causes.

Syntax

=TRIM(text)

text: The text, or the cell, to clean.

Basic examples

=TRIM("   Hello    World   ")

Returns Hello World: leading and trailing spaces removed, and the four spaces between the words reduced to one.

=TRIM(A2)

Cleans the text in A2.

What TRIM removes (and what it doesn't)

TRIM removes the regular space character (code 32): leading, trailing, and repeated inside the text.

It does not remove:

Non-breaking spaces (CHAR(160)), common in text copied from web pages
Line breaks (CHAR(10)) and tabs (CHAR(9))
Other invisible characters, such as zero-width spaces

To handle all of those, combine functions:

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

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

CLEAN removes non-printable characters, including line breaks. If you want line breaks turned into spaces rather than deleted, use SUBSTITUTE(A2, CHAR(10), " ") first.

Why spaces break your formulas

A trailing space makes "Apple" and "Apple " different values:

VLOOKUP("Apple", ...) fails to find "Apple ".
COUNTIF(A:A, "Apple") doesn't count "Apple ".
UNIQUE lists both as separate items.
Comparisons A2 = B2 return FALSE.

To check for stray spaces, compare lengths:

=LEN(A2) <> LEN(TRIM(A2))

TRUE means the cell has extra spaces.

Practical patterns

Clean a whole column

=ARRAYFORMULA(TRIM(A2:A))

Add IF(A2:A = "", "", ...) to keep blanks blank:

=ARRAYFORMULA(IF(A2:A = "", "", TRIM(A2:A)))

Trim inside a lookup

=VLOOKUP(TRIM(E2), A2:C100, 3, FALSE)

Cleans the key so it matches even when someone typed a trailing space.

Trim the lookup column too

If the source column itself has spaces, trim both sides with an array:

=XLOOKUP(TRIM(E2), ARRAYFORMULA(TRIM(A2:A100)), B2:B100)

Combine with PROPER or LOWER

=PROPER(TRIM(A2))

=LOWER(TRIM(A2))

A typical cleaning pipeline for names and emails.

Trim after SPLIT

SPLIT often leaves spaces around the pieces:

=ARRAYFORMULA(TRIM(SPLIT(A2, ",")))

Replace the original data with the cleaned data

Formulas leave the source unchanged. To overwrite the original: copy the TRIM results, then use Edit > Paste special > Values only over the original cells. Or use Data > Data cleanup > Trim whitespace for a one-time clean without formulas.

Video Example

Common problems

TRIM seems to do nothing. The "space" is probably a non-breaking space (CHAR(160)). Use the SUBSTITUTE version above.
Numbers become text. TRIM returns text. If you trim " 42 ", you get the text "42". Wrap in VALUE to turn it back into a number.
Leading zeros. If the cell is formatted as a number, 007 is already 7 before TRIM sees it.
Line breaks remain. Use CLEAN or SUBSTITUTE(..., CHAR(10), " ").

TRIM vs. CLEAN vs. REGEXREPLACE

TRIM: spaces only.
CLEAN: removes non-printable characters and line breaks.
REGEXREPLACE: the most flexible, for example REGEXREPLACE(A2, "^\s+|\s+$", "") trims all kinds of whitespace, and REGEXREPLACE(A2, "\s+", " ") collapses any whitespace to a single space.

Frequently asked questions

Does TRIM remove spaces between words? It reduces multiple spaces to one but keeps a single space between words.

Does TRIM change the original cell? No. Use paste-special values or Data > Data cleanup > Trim whitespace to change the source.

Does it work on numbers? It converts them to text first, so use VALUE afterward if needed.

Why does my lookup still fail after TRIM? The data may contain non-breaking spaces, different capitalization (lookups ignore case), or numbers stored as text.

Related Functions

SUBSTITUTE: Replace specific text.
REGEXREPLACE: Replace text by pattern.
LEN: Return the length of a text.
PROPER: Capitalize each word.
LOWER: Convert text to lowercase.

TRIM