Course / Working with Text

LEFT, RIGHT, MID and LEN

Cut region, year and number out of structured reference codes. Intermediate · 4 min · LEFT, RIGHT, MID, LEN

Codes often pack several facts into one string. A permit reference like NY-2024-0581 holds a region, a year and a sequence number. Three functions cut pieces out by position:

• LEFT(text, n) takes the first n characters
• RIGHT(text, n) takes the last n characters
• MID(text, start, n) takes n characters, beginning at position start

A fourth, LEN(text), counts the characters. It is the quickest way to check that every code really follows the pattern.

Steps

1. In B2:B8, pull the two-letter region from the start of each reference with LEFT.

2. In C2:C8, take the four-digit sequence number from the end of each reference with RIGHT.

3. The year sits in the middle: it starts at character 4 and is 4 characters long. In D2:D8, extract it with MID.

4. Every valid reference is 12 characters long. In E2:E8, count the characters in each one with LEN, and look for the odd one out.

Remember

• LEFT and RIGHT take characters from either end.
• MID(text, start, length) takes characters from anywhere in the middle.
• LEN counts characters, which makes badly formed codes easy to spot.
• The results are text. Use VALUE() to turn them into numbers.

Previous: Joining text · Next: Splitting text at a character