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

By Ali, Founder, Excel Lessons

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.

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

Extracting text means finding where a piece starts and how long it is.

In B2, type:

=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

    Extracting text means finding where a piece starts and how long it is. In B2, type: =MID(A2,6,2).

    Hint. Count the characters before the country.

  2. 2

    In B3, take the year from A3, using MID.

    Hint. Eight characters come before the year.

  3. 3

    In B4, take the surname from A4, using MID and FIND.

    Hint. Find the space first.

  4. 4

    A5 has hidden spaces you cannot see. Read =LEN(A5) and say what it returns.

    Hint. Spaces at both ends.

  5. 5

    Not every text has a space to split on. In B5, say whether A2 contains a space, using ISNUMBER and FIND. TRUE means there is a space; FALSE means there is not.

    Hint. ISNUMBER around FIND, looking for a space.

  6. 6

    In B6, count the characters in A2 with LEN.

    Hint. LEN takes one argument.