What TRIM Does
The TRIM function removes leading spaces, trailing spaces, and any repeated internal spaces from a text string, leaving only single spaces between words. It is one of the most useful data-cleaning functions because imported or pasted data frequently carries invisible whitespace that breaks lookups, comparisons, and sorting.
Syntax
=TRIM(text)
| Parameter | Description |
|---|---|
| text | The string or cell reference you want to clean up. |
That is it — just one argument. TRIM handles the rest.
Basic Examples
Remove leading and trailing spaces
| A | |
|---|---|
| 1 | Hello World |
=TRIM(A1)
Result: Hello World
The three leading spaces and two trailing spaces are gone.
Fix double spaces between words
| A | |
|---|---|
| 1 | New York City |
=TRIM(A1)
Result: New York City
Multiple spaces between words are collapsed to single spaces.
Clean data before a VLOOKUP
Imagine you are trying to match product codes and getting #N/A errors. The lookup table has "SKU-100" but your source data contains " SKU-100 ". Wrapping both sides with TRIM solves the mismatch:
=VLOOKUP(TRIM(A2), TRIM(D2:E50), 2, FALSE)
Note: When applying TRIM to a range inside VLOOKUP, you may need an array formula or clean the lookup column separately. A simpler approach is to TRIM just the lookup value.
Advanced Examples
Combine TRIM with SUBSTITUTE to remove all spaces
TRIM keeps single spaces between words. If you need to strip every space entirely (for example, to normalize a phone number), chain SUBSTITUTE after TRIM:
=SUBSTITUTE(TRIM(A1), " ", "")
For A1 = " 555 867 5309 ", this returns 5558675309.
Remove non-breaking spaces
TRIM only handles regular spaces (character 32). Data copied from websites often includes non-breaking spaces (character 160). To handle both:
=TRIM(SUBSTITUTE(A1, CHAR(160), " "))
This first converts non-breaking spaces to regular spaces, then lets TRIM clean everything up. It is a common pattern when working with web-scraped data.
Common Mistakes
- Assuming TRIM removes all whitespace characters. TRIM only strips regular spaces (ASCII 32). Tabs, newlines, and non-breaking spaces are left untouched. Use CLEAN or SUBSTITUTE for those.
- Using TRIM on numbers. If a cell contains an actual number (not text that looks like a number), TRIM has no effect. If a number is stored as text with extra spaces, TRIM will clean it but the result is still text. Wrap with VALUE to convert:
=VALUE(TRIM(A1)). - Forgetting that TRIM does not modify the original cell. Like all spreadsheet functions, TRIM returns a result in a new cell. The source cell keeps its original messy content. If you want to replace the originals, paste the trimmed values back using Paste Special > Values Only.
SheetAI Tip
Don't want to memorize this syntax? With SheetAI, just type what you need in plain English and the formula is generated for you. Install SheetAI to try it free.