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.
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.
- 1Take the prefix with LEFTThe first three letters of the product code in A2 name the warehouse. In B2, use LEFT to get them.
- 2Take the last digits with RIGHTPremiumIn B2, use RIGHT to get the last four characters of the invoice in A2, the running number.
- 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.
- 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).