Lessons Lesson 64

TRIM: The Invisible Problem

About this lesson

TRIM removes spaces from the start and end of a piece of text. It also shrinks any run of spaces inside it down to one.

By the end you can

  • remove stray spaces with TRIM.
  • say why a lookup fails on text with a trailing space, and fix it.

Practises TRIM LEN VLOOKUP

By Ali, Founder, Excel Lessons

The idea

One argument, and it fixes the single most expensive kind of bad data there is. A value with a trailing space looks exactly like the same value without one. Nothing shifts on screen, and nothing is highlighted. But it is not equal to it. So the lookup returns #N/A, the comparison returns FALSE, and the SUMIF adds nothing. Every one of those looks like a formula problem.

This is the answer to a question lesson 8 raised. The vlookup-errors lesson teaches that one of the four causes of #N/A is a trailing space left by a copy-paste. TRIM is what you wrap round the messy side to make the two match. One more thing to know: TRIM also shrinks double spaces in the middle. That is nearly always welcome and sometimes not. If the spacing inside a value carries meaning, TRIM will change it without asking.

The mistake to watch for

Expecting TRIM only to touch the ends. It also shrinks any run of spaces inside the text to a single space. That is welcome in a name and unwelcome in a value where the spacing means something. And a value that looks clean can still be dirty. Measure with LEN before and after, because the cell looks the same either way.

Read more about this

Where this comes up again

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

LEN counts every character, spaces included.

In E2, type:

=LEN(D2)

E2
Row A B C D E F G
1 Site Manager Tidied As typed
2 Kiln Lane R Okafor Kiln Lane
3 Bell Wharf L Hastie Bell Wharf
4 Ford Street M Kaur Ford Street
5 Ash Rise J Devlin
6
7
8

Every step

  1. 1

    LEN counts every character, spaces included. In E2, type: =LEN(D2).

    Hint. Count what is in the cell.

  2. 2

    TRIM removes spaces from the start and end. In C2, remove the outside spaces from D2, using TRIM.

    Hint. TRIM takes a cell.

  3. 3

    D4 has two spaces in the middle of it. Read =LEN(TRIM(D4)) and say what it returns.

    Hint. Count the spaces before and after.

  4. 4

    C6 looks up Kiln Lane but returns #N/A, though the site is listed. Fix C6. Remember: TRIM removes a hidden trailing space before you compare text.

    Hint. Wrap the messy side.

  5. 5

    In C3, count the outside spaces in D3. Shape: =LEN(cell)-LEN(TRIM(cell)).

    Hint. Two lengths, subtracted.