Text Functions
Intermediate

Measure what TRIM removes

LEN before and after TRIM shows how dirty a column really is.

Task:

Before cleaning an imported column you want to know how bad it is. In B2:B6 put the number of extra spaces in each entry — the characters TRIM would remove.

Interactive Spreadsheet

Read about TRIM

Hints

+5 XPno-hints bonus

Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.

Variations of TRIM

0/3

The same formula in the other shapes it takes at work.

  1. TRIM Function

    Remove extra spaces from text using TRIM.

    Core
    Beginner
  2. TRIM before you compare

    Two cells that look identical, and a comparison that says they are not.

    Intermediate
  3. Measure what TRIM removes

    LEN before and after TRIM shows how dirty a column really is.

    Intermediate

The data in this exercise

This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.

6 rows × 2 columns5 cells you fill in
AB
1Imported nameExtra spaces
2 Mira Patel
3Owen Hughes
4Sana Malik
5 Theo Grant
6Ugo Ferri
What this exercise teachesMay contain the answer

A clean entry scores 0, so the column doubles as a check: sort by it and the worst rows come to the top. It also tells you whether a problem is a stray space or something TRIM cannot touch, such as a non-breaking space from a web page, which would still score 0.