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

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.

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

The supplier name in A2 arrived with spaces around it and no capitals. In B2, fix both at once.

To begin, type it exactly:

=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

    The supplier name in A2 arrived with spaces around it and no capitals. In B2, fix both at once. To begin, type it exactly: =PROPER(TRIM(A2)).

    Hint. One function wraps the other.

  2. 2

    In B3, rewrite the purchase order in A3 with hyphens instead of slashes.

    Hint. Text, old, new.

  3. 3

    B4 was meant to turn the imported reference in A4 into a number and add one to it. It does no converting at all. Repair it.

    Hint. One function around A4.

  4. 4

    In C5, build a full name from A5 and B5 with a space between them. Use CONCAT.

    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. Count the characters, spaces between words included.

  6. 6

    One last conversion. In C3, show the length of the rewritten purchase order as text with no decimal places.

    Hint. Length first, then format it.