Function guides · Text
TRIM function
Removes extra spaces from text, leaving single spaces between words.
Syntax
=TRIM(text)
| Argument | What it is |
|---|---|
text | The text or cell to clean. |
What TRIM does
TRIM tidies text by removing spaces you cannot see. It deletes any space at the start and end of the text and shrinks every run of spaces inside it to one.
Two cells that look the same can fail to match because one has a trailing space. VLOOKUP, MATCH, COUNTIF and conditional formatting all treat “West” and “West “ as different. TRIM is the first step in cleaning imported data.
The usual routine is to clean into a helper column, check the result, then copy and paste it back as values. Leaving the formulas in place is fine for a report that refreshes from a feed, since new rows are cleaned automatically.
TRIM leaves three things alone. It does not change capitalization (UPPER, LOWER and PROPER do that), it does not remove characters other than spaces, and it does not turn text into numbers.
TRIM examples
1. Strip leading and trailing spaces
| A | B | |
|---|---|---|
| 1 | Raw name | Clean |
| 2 | Mei Tanaka | Mei Tanaka |
The name in A2 has two spaces before and one after, plus a doubled space in the middle. TRIM removes the leading and trailing spaces and reduces the double space between the words to one.
2. Fix a lookup that should have matched
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | SKU | Price | Key | ||
| 2 | A-10 | 12.5 | B-22 | 34 | |
| 3 | B-22 | 34 |
The lookup key typed as "B-22 " with a trailing space does not match "B-22" in the table, and VLOOKUP returns #N/A. Trimming the key inside the formula fixes it without editing the data.
3. Count the spaces TRIM removed
| A | B | C | |
|---|---|---|---|
| 1 | Raw | ||
| 2 | a b c | 4 |
Comparing LEN before and after shows how much clutter there was. The raw text is 9 characters and the trimmed text is 5, so four spaces were removed.
Common TRIM mistakes
- Non-breaking spaces. Text copied from a web page often contains character 160, which TRIM leaves alone. Remove it with
=TRIM(SUBSTITUTE(A2,CHAR(160)," ")). - Trimming and leaving the formulas behind. The cleaned column is a formula. To keep the values, copy it and use Paste Special > Values before deleting the raw column.
- Expecting it to remove every space. TRIM keeps one space between words. To remove all spaces, use SUBSTITUTE(A2," ","").
- Other invisible characters. Line breaks need CLEAN. Use
TRIM(CLEAN(A2))for text with both.
TRIM on Excel job tests
When a prompt says "the lookup fails even though the values look identical", suspect spaces. Imported data often carries leading, trailing and doubled spaces, and a VLOOKUP across untrimmed keys returns #N/A for exactly those rows.
Two habits help. Compare LEN with what you see: a cell that looks like four characters and measures five has a space in it. And clean into a new column, then convert to values, rather than overwriting data with formulas that point at themselves.
If the data looks pasted from a web page and TRIM changes nothing, try SUBSTITUTE with CHAR(160) first.
Related functions
TRIM questions
- What does TRIM do in Excel?
- It removes leading and trailing spaces and reduces any run of spaces between words to a single space.
- Why does TRIM not remove all my spaces?
- The spaces may be non-breaking spaces (character 160). Substitute them with ordinary spaces first.
- How do I remove all spaces in a cell?
- Use SUBSTITUTE(A2," ","").
- Does TRIM change numbers?
- It returns text. A trimmed number needs VALUE to become a number again.
Practice TRIM
Try it in a working grid: the free lesson LEN and TRIM: measure and clean (module 9 of Everyday Excel) has checked TRIM exercises.
Practice this function