Excel Practice
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

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.

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

Column D holds the site names exactly as somebody typed them into a form. The one in D2 looks nine characters long. Before cleaning anything, measure it. LEN counts what is really in the cell.

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

    Column D holds the site names exactly as somebody typed them into a form. The one in D2 looks nine characters long. Before cleaning anything, measure it. LEN counts what is really in the cell. In E2, type =LEN(D2).

    Hint. Count what is in the cell.

  2. 2

    Clean it up. In C2, give the site name in D2 with the outside spaces removed.

    Hint. One argument.

  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 the manager for the site named in D2 and returns #N/A. Kiln Lane is clearly in the list. What D2 holds has a trailing space. Repair the formula so it finds the row.

    Hint. Wrap the messy side.

  5. 5

    Count what TRIM would remove. In C3, give the number of spaces at the ends of the site name as typed in D3.

    Hint. Two lengths, subtracted.