How to Use TRIM in Google Sheets
=TRIM(text).
What TRIM does
Imported or pasted data often carries invisible spaces. They are the usual reason a lookup or count fails even though two cells look identical. TRIM cleans the text so that it matches.
TRIM syntax
=TRIM(text)
text is a string or a reference to a cell that holds one. Per Google's help page, TRIM removes spaces at the start and end of the text and repeated spaces between words, leaving one space between words. It does not remove non-breaking spaces or other whitespace characters. Sheets already trims text you type directly into cells, so the problem appears mostly in pasted and imported data.
A step-by-step example
Cell A2 holds a name with two spaces at the start, three between the words and two at the end. Cell A3 holds Done followed by one trailing space.
| A | |
|---|---|
| 1 | Text |
| 2 | Ann Lee |
| 3 | Done |
To clean the name:
=TRIM(A2)
This returns Ann Lee, with no outer spaces and a single space in the middle.
Why a lookup fails without TRIM
A3 looks like Done but is not equal to it:
=A3="Done"
This returns FALSE, because of the trailing space. With TRIM it matches:
=TRIM(A3)="Done"
This returns TRUE. The same idea fixes COUNTIF, VLOOKUP and UNIQUE when a stray space gets in the way.
Common TRIM mistakes
- Non-breaking spaces. Text copied from web pages may contain them. TRIM leaves them alone, but SUBSTITUTE can replace them.
- Trimming the source cell by hand. The formula returns a cleaned copy. Copy and paste it as values if you want to replace the original.
- Forgetting that numbers stay text. Trimmed digits may still be text. Wrap in VALUE if needed.
Try it yourself
Use the cells above. Type your formula into the box, starting with =, then press Check.
Exercise 1: Clean the name
Write a formula that removes the extra spaces from the text in A2.
Exercise 2: Make the match work
Write a formula that checks whether the trimmed text in A3 equals Done. It should return TRUE.
What to learn next
- SUBSTITUTE: replace text inside a string.
- COUNTIF: count the rows that match a condition.
- VLOOKUP: fetch one value from a table.
- Remove duplicates: two ways to clean a list.
- LEN: count the characters in a cell.
Syntax and rules in this guide were checked against Google's TRIM help page. See all guides.