Spreadsheet Practice

Lesson 13 of 29, Text cleanup

LEFT, RIGHT, MID & LEN

Cut text apart by position.

Pro8 minutes, 3 exercisesLEFTRIGHTMIDLEN

Watch it done

3 steps

Watch the technique worked out on sample data, then try it yourself in the exercises.

How it works

LEFT(text, n) takes n characters from the start, RIGHT(text, n) from the end, and MID(text, start, n) from the middle. LEN counts the characters, which helps when pieces vary in length.

The results are text, even when they look like numbers: "0451" keeps its leading zero.

Syntax

=LEFT(A2,2)First two characters
=RIGHT(A2,4)Last four
=MID(A2,4,4)Four characters starting at position 4
=LEN(A2)Character count

Watch out for

  • Wrap the result in VALUE() if you need to do math with it.
  • Count positions carefully: in NY-2026-0451 the year starts at character 4.

The exercises

  1. City code. In B2, extract the two-letter city code from the order code. Fill down to B6.
  2. Sequence number. In C2, extract the last four characters. Fill down to C6.
  3. Year from the middle. In D2, extract the four-digit year from the middle of the code. Fill down to D6.

Graded practice for this lesson is part of Pro

The walkthrough and notes are open to everyone. Pro unlocks the exercises here and in the other 27 Pro lessons, Excel and Google Sheets, 79 exercises in all.

  1. City code
  2. Sequence number
  3. Year from the middle
Try these functions in the Formula Lab