Excel Practice
Lessons Lesson 56

MID: Taking from the Middle

About this lesson

MID returns a run of characters from anywhere inside a piece of text. You give it where to start and how many to take.

By the end you can

  • take characters from the middle of a cell with MID.
  • count a starting position and a length without an off-by-one.

Practises MID LEFT RIGHT FIND

The idea

Three arguments: the text, the starting position, and the length. Start at the fourth character of BK-2024-0031 and take four, and you have the year. Excel counts characters from 1, so the B is character 1. Anybody used to a programming language will be off by one until that sinks in.

MID never complains about going past the end. Start past the end of the text and you get an empty string, not an error. That is the failure to expect. The shape of the data changes: a code gains a character, or an import drops a prefix. Then a whole column of extractions goes blank instead of turning red. Blank cells where you expected values almost always mean the positions moved. The data did not go missing.

The mistake to watch for

A start position typed as a number works until the data changes shape. A code that gains a character, or an import that drops a prefix, moves every piece by one. MID does not complain. It returns the wrong characters, or an empty string if it starts past the end. A column of extractions that goes blank means the positions moved, not that the data went missing.

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

Every booking reference has the year in the middle. Pull the four-digit year out of A2 into B2.

To begin, type it exactly:

=MID(A2,4,4)

B2
Row A B C D E F G
1 Booking Year Sequence
2 BK-2024-0031
3 BK-2023-0147
4 BK-2025-0008
5
6 Year starts at 4
7 Year length 4
8

Every step

  1. 1

    Every booking reference has the year in the middle. Pull the four-digit year out of A2 into B2. To begin, type it exactly: =MID(A2,4,4).

    Hint. Three arguments this time.

  2. 2

    The start position counts from one, not from zero. Read =MID(A2,1,2) and say what it returns.

    Hint. What is character number 1?

  3. 3

    Pull the four-digit sequence off the end of A3, into C3, using MID.

    Hint. Count up to where the sequence begins.

  4. 4

    Write it so it survives a change to the format. In B4, take the year out of A4. Use the start position in B6 and the length in B7.

    Hint. Three arguments, two of them references.

  5. 5

    Somebody starts past the end of the text. Read =LEN(MID(A2,20,4)) and say how long what comes back is.

    Hint. There is no character 20.