Excel practice › Excel functions › TRIM
TRIM in Excel: remove the spaces that break lookups
TRIM removes the spaces at the start and at the end of a text, and cuts any run of spaces between words down to one. =TRIM(A2) turns " Harlow Paper " into "Harlow Paper".
It matters because Excel compares text character by character. A name with one space after it is a different value from the same name without it, so lookups and comparisons fail on cells that look identical.
Syntax
=TRIM(text)
text- The text to clean: a cell, or text in quotes. Single spaces between words are kept. The result is text, also when the cell held a number.
| A | B | |
|---|---|---|
| 1 | Vendor (as pasted) | Clean |
| 2 | Harlow Paper | Harlow Paper |
| 3 | Ridgeline Freight | |
| 4 | Summit Coffee | |
| 5 | Delta Uniform | |
| 6 | Kestrel Print |
=LEN(A2), then to =LEN(TRIM(A2)).Values that look equal and aren't
To Excel, Summit Coffee and Summit Coffee followed by a space are two different texts. A lookup for one doesn't find the other, and nothing on screen shows why: a space at the end of a cell is invisible.
It happens with data that was typed by hand, pasted from an email or exported from another system. In the sheet below, the name in E2 was pasted with a space at the end:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Vendor | Terms | Balance | Find vendor | Balance | |
| 2 | Harlow Paper | Net 30 | 4200 | Summit Coffee | #N/A | |
| 3 | Ridgeline Freight | Net 15 | 1850 | |||
| 4 | Summit Coffee | Net 30 | 640 | |||
| 5 | Delta Uniform | Net 45 | 2975 | |||
| 6 | Kestrel Print | Net 30 | 1120 | |||
| 7 | Bayside Electric | Net 15 | 5310 |
Summit Coffee plus a space, and that text is nowhere in column A. Clean the value inside the formula: =VLOOKUP(TRIM(E2),A2:C7,3,FALSE) returns 640.How to prove the space is there. Count the characters with LEN. If the count is higher than what you can see, something invisible is in the cell:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Vendor | Terms | Balance | Find vendor | Length | |
| 2 | Harlow Paper | Net 30 | 4200 | Summit Coffee | 14 | |
| 3 | Ridgeline Freight | Net 15 | 1850 | |||
| 4 | Summit Coffee | Net 30 | 640 | |||
| 5 | Delta Uniform | Net 45 | 2975 | |||
| 6 | Kestrel Print | Net 30 | 1120 | |||
| 7 | Bayside Electric | Net 15 | 5310 |
=LEN(TRIM(E2)) gives 13, and =E2=A4 gives FALSE: the two cells are not equal.When the spaces are in the table
Trimming the lookup value doesn't help when the stray spaces are in the column being searched. There are two fixes:
- Clean the column once. Add a helper column with
=TRIM(A2), fill it down, copy it, paste it back as values, then delete the original column. - Trim the column inside the formula.
=XLOOKUP(E2,TRIM(A2:A7),C2:C7)searches a cleaned copy of the names. Exercise 3 is this case.
The space TRIM doesn't remove
TRIM removes the ordinary space character. Text copied from web pages often holds a different one, the nonbreaking space (character 160), and Microsoft's page says TRIM by itself does not remove it. If LEN still shows an extra character after TRIM, replace that character with an ordinary space first: =TRIM(SUBSTITUTE(A2,CHAR(160)," ")).
This case can't be shown live here: the sheets on this page don't run SUBSTITUTE, and their TRIM is less strict than Excel's about that character.
Practice: 3 exercises
Type the formula in the box, press Enter. Clicking a cell inserts its address. Each answer is checked on the data you see and again on changed data, so a typed-in value won't pass.
The names in column A were pasted from an email, with stray spaces. In B2, return the name in A2 without them.
| A | B | |
|---|---|---|
| 1 | Vendor (as pasted) | Clean |
| 2 | Harlow Paper | |
| 3 | Ridgeline Freight | |
| 4 | Summit Coffee | |
| 5 | Delta Uniform | |
| 6 | Kestrel Print |
Show the answer
=TRIM(A2) gives Harlow Paper.
TRIM drops the spaces at both ends and leaves one between the words: 12 characters instead of 17, and a value that matches the same name anywhere else.
Someone pastes a vendor name in E2, and this time it came with a space in front. In F2, return that vendor's balance. The formula has to work whether E2 has extra spaces or not.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Vendor | Terms | Balance | Find vendor | Balance | |
| 2 | Harlow Paper | Net 30 | 4200 | Delta Uniform | ||
| 3 | Ridgeline Freight | Net 15 | 1850 | |||
| 4 | Summit Coffee | Net 30 | 640 | |||
| 5 | Delta Uniform | Net 45 | 2975 | |||
| 6 | Kestrel Print | Net 30 | 1120 | |||
| 7 | Bayside Electric | Net 15 | 5310 |
Show the answer
=VLOOKUP(TRIM(E2),A2:C7,3,FALSE) gives 2975.
TRIM(E2) hands the lookup the name without the stray space, and that text is in column A. On a clean name TRIM changes nothing, so the formula is safe to use everywhere.
Now the vendor names in column A came out of an export with stray spaces, and the name in E2 is clean. In F2, return the balance for the vendor in E2 without changing column A: trim the names inside the formula.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Vendor | Terms | Balance | Find vendor | Balance | |
| 2 | Harlow Paper | Net 30 | 4200 | Ridgeline Freight | ||
| 3 | Ridgeline Freight | Net 15 | 1850 | |||
| 4 | Summit Coffee | Net 30 | 640 | |||
| 5 | Delta Uniform | Net 45 | 2975 | |||
| 6 | Kestrel Print | Net 30 | 1120 | |||
| 7 | Bayside Electric | Net 15 | 5310 |
Show the answer
=XLOOKUP(E2,TRIM(A2:A7),C2:C7) gives 1850.
TRIM(A2:A7) hands the lookup a cleaned copy of the six names, and XLOOKUP returns the balance from the same position in C2:C7. This needs an Excel version with XLOOKUP and dynamic arrays (Microsoft 365, or Excel 2021 and later). In older versions, clean the column once with a helper column.
Questions people ask
Why is TRIM not removing spaces in Excel?
Most often because the character isn't an ordinary space. Text copied from web pages can hold nonbreaking spaces (character 160), which TRIM leaves alone. Replace them first, then trim: =TRIM(SUBSTITUTE(A2,CHAR(160)," ")). The other usual cause: the formula is fine, but the lookup or comparison still points at the original column and not at the trimmed one.
Does TRIM remove the spaces between words?
It keeps one. Spaces at the start and at the end go, and several spaces in a row between words become a single space. Delta Uniform typed with two spaces in the middle comes back with one.
How do I check whether a cell has a trailing space?
Compare its length before and after: =LEN(A2)-LEN(TRIM(A2)) returns the number of extra spaces, and 0 means the cell is clean. =A2=TRIM(A2) answers the same question with TRUE or FALSE: FALSE means there is something to remove.
How do I remove the spaces for good, without keeping the formula?
Put =TRIM(A2) in a helper column and fill it down. Copy the helper column and paste it as values, then delete the original column. The cleaned text is now plain data with no formula behind it.