SheetsdrillTRIM
TRIM(text) removes spaces at the start and end of a text and collapses runs of spaces inside it to a single space. It is the first fix when lookups fail on values that look identical, and ARRAYFORMULA(TRIM(A2:A)) cleans a whole column at once.
Syntax
=TRIM(text)| Part | What it is |
|---|---|
text | The cell or string to clean. |
Examples
=TRIM(" Ana Lopez ")Result: Ana Lopez
=ARRAYFORMULA(TRIM(A2:A100))Result: A clean copy of the whole column
=VLOOKUP(TRIM(A2), Products!A:C, 3, FALSE)Result: A lookup that still works when A2 has a trailing space
Common mistakes
- TRIM does not remove non-breaking spaces (CHAR(160)) pasted from web pages; use SUBSTITUTE(A2, CHAR(160), " ") inside TRIM.
- Line breaks and tabs stay; wrap in CLEAN to remove them: TRIM(CLEAN(A2)).
- TRIM returns text, so a trimmed number no longer sums; wrap in VALUE.
Practice TRIM
These drills check your answer on live data, free, no account needed.
- Address without gapsBuild one address line from columns A to D, separated by a comma and a space. Some rows have no unit, and the line must not show an empty gap. Fill E2 and E3 using TEXTJOIN.
Questions
How do I remove all spaces, not just extra ones?
=SUBSTITUTE(A2, " ", "") removes every space.
Is there a menu option to trim?
Yes, select the cells and use Data > Data cleanup > Trim whitespace; it changes the cells in place.
Related
- SUBSTITUTEHow SUBSTITUTE in Google Sheets replaces one piece of text with another, removes characters, targets one occurrence, and how it differs from REGEXREPLACE.
- LENHow LEN in Google Sheets counts the characters in a cell, how to find values that are too long or short, count words, and why hidden spaces change the number.
- CONCATENATE and &How to combine cells in Google Sheets with CONCATENATE, CONCAT and the & operator, add spaces and separators, keep number and date formats, avoid mistakes.
- VLOOKUPHow VLOOKUP works in Google Sheets: the four arguments, exact and approximate match, lookups from another sheet, and fixes for #N/A and wrong results.