Excel Practice
Lessons Lesson 60

Extracting Text: Putting It Together

About this lesson

Extracting text in Excel means deciding where a piece starts and how long it is. Count when the format is fixed. Find a character when it is not.

By the end you can

  • pull a code or a name out of a cell with LEN, LEFT, MID and FIND.
  • choose the extracting function from the shape of the text.

Practises LEFT RIGHT MID FIND LEN ISNUMBER

The idea

Two situations and two answers. A reference code with a fixed shape can be taken apart with plain numbers, because the positions never move. Anything written by a person cannot: a name, an address, a description. The lengths vary. There the position has to be found with FIND or SEARCH and handed to LEFT, RIGHT or MID.

The habit that separates a working sheet from a broken one is assuming the pattern will break. It will. One row in a few hundred has no space in it, or an extra hyphen, or came from a different system. Every extraction here fails on that row, and it takes the column with it. Testing with ISNUMBER first costs a few characters. It turns a red column into one cell you can go and look at.

The mistake to watch for

Assuming every row fits the pattern. One row in a few hundred has no space in it, has an extra hyphen, or came from a different system. Every extraction fails on that row and takes the column with it. Test for the character with ISNUMBER around FIND before you cut. Measure with LEN when a result that looks right will not match.

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

Take the two-letter country out of the shipment reference in A2, into B2.

To begin, type it exactly:

=MID(A2,6,2)

B2
Row A B C D E F G
1 Raw record Extracted
2 SHIP/GB/2024/0471
3 SHIP/IE/2023/1180
4 Nadia Kowalski
5 Hollow Lane
6
7
8

Every step

  1. 1

    Take the two-letter country out of the shipment reference in A2, into B2. To begin, type it exactly: =MID(A2,6,2).

    Hint. Count the characters before the country.

  2. 2

    Take the year out of A3, into B3.

    Hint. Eight characters come before the year.

  3. 3

    Take the surname out of the contact name in A4, into B4.

    Hint. Find the space first.

  4. 4

    A5 came in from a form and reads Hollow Lane, eleven characters. Read =LEN(A5) and say what it returns.

    Hint. Spaces at both ends.

  5. 5

    Make an extraction that checks before it cuts. The shipment references have no space in them. So a formula that splits A2 at a space would fail. In B5, say whether A2 contains a space at all, TRUE or FALSE, with ISNUMBER around FIND.

    Hint. ISNUMBER around FIND, looking for a space.

  6. 6

    In B6, use LEN to say how many characters are in the shipment reference in A2.

    Hint. One argument.