The TRIM function in Excel
TRIM cleans up spacing: it removes spaces at the start and end of a text and turns runs of spaces between words into a single one. Data copied from websites, forms or other systems is full of them, and you can't see them.
Invisible spaces are a classic reason why VLOOKUP or XLOOKUP return #N/A for a value that's clearly there.
Syntax
=TRIM(text)
| Argument | What it means |
|---|---|
| text | The text or cell to clean. |
Examples
1. Clean a name
| A | B | |
|---|---|---|
| 1 | As typed | Clean |
| 2 | Maya Cohen | Maya Cohen |
The spaces at both ends are gone, and the three spaces between the words became one.
2. Clean and fix capitals in one go
| A | B | |
|---|---|---|
| 1 | As typed | Clean |
| 2 | maya COHEN | Maya Cohen |
PROPER capitalizes each word after TRIM has fixed the spacing.
3. How many spaces were removed?
| A | B | |
|---|---|---|
| 1 | As typed | Extra spaces |
| 2 | Maya Cohen | 5 |
15 characters before, 10 after: 5 extra spaces.
Common mistakes
Spaces that TRIM doesn't remove. Text from web pages often contains non-breaking spaces (character 160), which TRIM leaves alone.
Fix: Replace them first:
=TRIM(SUBSTITUTE(A2,CHAR(160)," ")).Cleaning only one side of a lookup. The lookup value is clean, the table still has spaces.
Fix: Clean the table column too, e.g. with a helper column, then look up in that.
Frequently asked questions
How do I replace the original text with the cleaned version?
Copy the TRIM column, then Paste Special › Values over the original, and delete the helper column.
Related functions
- PROPERPROPER capitalizes the first letter of each word, UPPER makes text all caps, LOWER all small letters.
- SUBSTITUTESUBSTITUTE replaces specific text inside a cell with other text, everywhere or only one occurrence.
- LENLEN returns the number of characters in a text, spaces included.
- VLOOKUPVLOOKUP finds a value in the first column of a table and returns a value from the same row in another column.