LEFT, RIGHT and MID practice exercises

Take a piece out of a text by position: the first characters, the last characters or a run from the middle, such as a prefix or a year inside a code.

Start the first exercise

How LEFT, RIGHT and MID work

LEFT, RIGHT and MID cut a piece out of a text by position. LEFT takes characters from the start and RIGHT takes them from the end. Both need the text and how many characters to take. MID takes a run from the middle, so it needs one more argument: the position to start at. In all three, a space counts as a character.

If A2 holds the product code "OSL-2024-0173", =LEFT(A2,3) returns "OSL", =MID(A2,5,4) returns "2024" and =RIGHT(A2,4) returns "0173". The result is always text, even when it looks like a number. If you need to calculate with it, wrap the formula in VALUE or multiply it by 1.

Counting positions breaks down when the pieces have different lengths. To drop a 3-character prefix from any length of text, combine RIGHT with LEN: =RIGHT(A2,LEN(A2)-3). If the text has a separator like a dash or an @, use FIND to locate it instead of counting. The exercises below cover taking a prefix with LEFT, the last digits with RIGHT, a year from the middle with MID, and dropping a prefix with RIGHT and LEN.

  1. 1Take the prefix with LEFTThe first three letters of the product code in A2 name the warehouse. In B2, use LEFT to get them.
  2. 2Take the last digits with RIGHTPremiumIn B2, use RIGHT to get the last four characters of the invoice in A2, the running number.
  3. 3Take the year from the middle with MIDPremiumThe year in the invoice in A2 starts at character 5 and is 4 characters long. In B2, use MID to get it.
  4. 4Drop a prefix with RIGHT and LENPremiumEvery order ID starts with a 3-letter channel code, but the numbers have different lengths. In B2, take everything after the first 3 characters with RIGHT, and use LEN(A2)-3 for how many.

Functions in this topic

Read the guide

Questions

How do I get the text between two characters in Excel?

Use MID with FIND to locate both characters. If A2 holds "Sales (North)", =MID(A2,FIND("(",A2)+1,FIND(")",A2)-FIND("(",A2)-1) returns North. FIND gives the positions and MID cuts out what sits between them. In Excel 365 it is shorter: =TEXTBEFORE(TEXTAFTER(A2,"("),")").

How do I remove the first or last characters from a text in Excel?

To drop the first 3 characters, use =RIGHT(A2,LEN(A2)-3). To drop the last 3, use =LEFT(A2,LEN(A2)-3). LEN gives the total length, and you subtract what you want to remove. If a cell has fewer than 3 characters, the formula returns a #VALUE! error.

Why does LEFT, RIGHT or MID return text instead of a number?

These functions always return text, even when the characters are digits. A result like "2024" is then left-aligned, skipped by SUM and sorted as text. Convert it with VALUE, as in =VALUE(MID(A2,5,4)), or multiply by 1. A double minus works too: =--MID(A2,5,4).