The TRIM and CLEAN functions in Excel
TRIM removes stray spaces from text. CLEAN removes hidden characters that come with imported data.
What TRIM / CLEAN does
Data pasted from other systems often has extra spaces at the start or end, or double spaces between words. They are hard to see and they stop lookups and comparisons from matching. TRIM removes the spaces at the start and end and reduces any run of spaces between words to one.
CLEAN removes non-printing characters that exports sometimes leave behind. When a cell has both problems, use them together: =TRIM(CLEAN(A2)).
Syntax
=TRIM(text)
- text
- The text to tidy, usually a cell.
Worked example
| A | B | |
|---|---|---|
| 1 | Pasted name | Clean name |
| 2 | Elena Cole | Elena Cole |
| 3 | Carlos Dunn | Carlos Dunn |
| 4 | Priya Hughes | Priya Hughes |
| 5 | Tom Bell | Tom Bell |
TRIM removes the spaces at each end and shrinks the double space between the words to one.
Now you try
A fresh exercise every time. Press Another exercise as often as you like.
Where you will use it
Data cleanup
Fix names and codes that will not match in a lookup.
HR
Tidy names from an exported staff list.
Sales & Ops
Clean product codes before counting or totalling them.
Common mistakes
- Expecting TRIM to change the original cell. It returns a cleaned copy, so put the formula in another column, then paste the values back if needed.
- Using TRIM on a non-breaking space. Web pages sometimes contain a different kind of space that TRIM does not remove.
- Forgetting CLEAN. If TRIM seems to do nothing, the problem may be hidden characters rather than spaces.
The TRIM / CLEAN course
- 1Clean up stray spacesFree practice belowPractise
- 2Count the stray spacesMembers lesson
- 3Take the first letters after cleaningMembers lesson
- 4Remove hidden charactersMembers lesson
- 5Match after cleaningMembers lesson
Common questions
Why does my lookup fail even though the values look the same?
Check for stray spaces. Wrapping the lookup value in TRIM often fixes it.
Can I see how many spaces were removed?
Yes. Subtract the length of the trimmed text from the length of the original: =LEN(A2)-LEN(TRIM(A2)).
Related functions
Unlock every lesson
Every function has a free lesson. Members get all the lessons, fresh exercises every time and the weekly leaderboard. £7 a month or £49 a year, and you can cancel at any time.