The TRIM function in Excel
TRIM removes spaces from the start and end of text and squeezes any run of spaces between words down to one. Data exported from CRMs, web forms and PDFs is full of invisible extra spaces that break VLOOKUP, COUNTIF and duplicate checks. TRIM is usually the first step in any data-cleaning task.
Syntax
=TRIM(text)
| text | The text or cell to clean, like A2. |
|---|
Try it yourself
This is a live spreadsheet. Type the formula and press Enter.
These company names came from a CRM export with messy spacing. In C2, use TRIM to clean the name in A2.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Clean name | ||||||
| 2 | Kim | ||||||
| 3 | Ade | ||||||
| 4 | Kim | ||||||
| 5 | Rui | ||||||
| 6 | |||||||
| 7 | |||||||
| 8 |
Tip: TRIM only needs one argument: the cell you want to clean.
Examples
=TRIM(A2)→ "Maria Lopez"Removes the spaces in front of the name.
=LEN(A3)-LEN(TRIM(A3))→ 5Compares the length before and after cleaning to count how many extra spaces the cell had. Useful for auditing a column.
=PROPER(TRIM(A4))→ "North Wind Traders"Cleans the spacing and fixes the capitalization in one step.
Example data
| 1 | Raw name |
|---|---|
| 2 | Maria Lopez |
| 3 | James O'Neil |
| 4 | north wind traders |
Common mistakes
Spaces that TRIM won't remove
Text copied from web pages often contains non-breaking spaces, which TRIM ignores. Replace them first:
TRIM(SUBSTITUTE(A2,CHAR(160)," ")).Lookups still fail after cleaning
Both sides of a lookup need to match. If the table has trailing spaces too, clean that column as well, or trim the lookup value:
VLOOKUP(TRIM(F2),...).Leaving formulas behind
TRIM gives you a cleaned copy in a new column. To replace the original, copy the results and use Paste Special > Values, then delete the helper column.
FAQ
Why isn't TRIM removing spaces?+
The cell probably contains non-breaking spaces (character 160) rather than normal spaces. Swap them with SUBSTITUTE, then TRIM.
Does TRIM remove the single spaces between words?+
No. It keeps one space between words and only removes the extras, plus everything at the start and end.
How do I TRIM a whole column?+
Put =TRIM(A2) in a helper column, fill it down, then paste the results back over the original as values.
Lesson
Master TRIM step by step: Cleaning messy text
Fix stray spaces, random capitals and inconsistent separators in imported data with TRIM, PROPER, UPPER, LOWER and SUBSTITUTE.