TRIM Function in Google Sheets
Removes leading, trailing, and duplicate internal whitespace from a text value. The first cleanup function to reach for when imported data has hidden spaces breaking your lookups.
Syntax
TRIM(text)Returns: text with all surrounding whitespace removed and internal runs of whitespace collapsed to single spaces.
Excel equivalent: TRIM (similar — Excel TRIM doesn't collapse internal spaces, Google Sheets TRIM does)
Parameters
| Name | Required | Description |
|---|---|---|
| text | Required | The string to clean. |
Examples
Clean a single cell
=TRIM(A2)Removes leading/trailing spaces and collapses multiple internal spaces to one.
Apply to a whole column
=ARRAYFORMULA(TRIM(A2:A1000))Trims every cell in the column at once. Useful for cleaning a freshly imported range.
Trim inside a VLOOKUP to fix #N/A
=VLOOKUP(TRIM(A2), Products!A:B, 2, FALSE)Common fix when VLOOKUP fails because A2 has a trailing space that doesn't appear in the lookup table.
When to use an alternative
- CLEAN — Your text has non-printing characters (newlines, tabs) — TRIM only handles spaces.
- SUBSTITUTE — You want to remove a specific character (not whitespace).
- REGEXREPLACE — You need surgical whitespace handling (e.g., trim only one side).
Common errors and how to fix them
Trims but VLOOKUP still fails
Cause: Source data has invisible non-space characters (CHAR(160) non-breaking space is common from web copies).
Fix: Chain TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " "))) for paranoid cleaning.
Related functions
Frequently Asked Questions
Why does VLOOKUP fail even after TRIM?
Likely a non-breaking space (CHAR(160)) — common when data is pasted from the web. TRIM only removes regular spaces. Use SUBSTITUTE(text, CHAR(160), " ") before TRIM, or wrap with CLEAN to also handle other invisible characters.
Does TRIM affect numbers?
Numbers don't have whitespace, so TRIM is a no-op. But if a cell contains a number stored as text (" 100 "), TRIM converts to "100" which Sheets may then recognize as a real number depending on the destination cell format.