Lessons Lesson 67

Building and Cleaning: Putting It Together

About this lesson

Cleaning imported text usually means four things. Trim the spaces, fix the case, replace the separators, and convert anything numeric back into a number.

By the end you can

  • clean an imported column with TRIM, SUBSTITUTE, PROPER and VALUE.
  • build a label from several cells with & and TEXT.

Practises TRIM SUBSTITUTE PROPER VALUE TEXT CONCAT LEN

By Ali, Founder, Excel Lessons

The idea

The order matters less than doing all four. But trim first. It removes the thing that stops values matching, and everything after it is easier to check. Functions nest from the inside out, so the one written innermost runs first.

The part people skip is checking. Cleaning is invisible work. The cell looked fine before and looks fine after. So it is entirely possible to apply TRIM to the wrong column and never find out. Comparing a length before and after, or asking ISNUMBER whether a conversion really converted, takes one formula. It turns a belief into a fact. Every cleaning step in this module has a matching check, and the check is the half to keep on the sheet.

The mistake to watch for

Cleaning without checking. The cell looked fine before and looks fine after. So TRIM applied to the wrong column, or a conversion that never converted, is invisible. Compare a length before and after, or ask ISNUMBER whether the number is now a number. One formula turns a belief that the cleaning worked into a fact.

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

Nest functions to fix two problems in one formula.

In B2, type:

=PROPER(TRIM(A2))

B2
Row A B C D E F G
1 Raw Cleaned Check
2 ashworth & co
3 PO/2024/0417
4 0088
5 Dilys Pritchard
6
7
8

Every step

  1. 1

    Nest functions to fix two problems in one formula. In B2, type: =PROPER(TRIM(A2)).

    Hint. One function wraps the other.

  2. 2

    In B3, replace every slash in A3 with a hyphen.

    Hint. Text, old, new.

  3. 3

    B4 should add 1 to the number in A4, but A4 holds text. Fix B4. Remember: VALUE turns text into a number.

    Hint. One function around A4.

  4. 4

    CONCAT joins pieces of text together. In C5, join A5 and B5 with a space, using CONCAT. Remember: the space between them needs quotes too.

    Hint. Nothing appears between two joined pieces unless you put it there.

  5. 5

    Check the tidying worked. Read =LEN(TRIM(A2)) and say what it returns.

    Hint. The space between the words still counts.

  6. 6

    In C3, show the length of A3 as text, no decimals. Shape: =TEXT(LEN(cell),"0").

    Hint. Length first, then format it.