The Formula Gym
XP 0
Streak 0

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

AB
1Pasted nameClean name
2 Elena Cole Elena Cole
3Carlos DunnCarlos Dunn
4 Priya HughesPriya Hughes
5Tom Bell Tom Bell
B2 =TRIM(A2) (copied down 4 rows)

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

  1. 1
    Clean up stray spacesFree practice below
    Practise
  2. 2
    Count the stray spacesMembers lesson
  3. 3
    Take the first letters after cleaningMembers lesson
  4. 4
    Remove hidden charactersMembers lesson
  5. 5
    Match 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.

See membership