Excel practice › Excel functions › LEFT, RIGHT, MID
LEFT, RIGHT and MID in Excel: pull part of a text out of a cell
LEFT, RIGHT and MID return part of a text: LEFT takes characters from the start, RIGHT from the end, MID from any position you name. For a pallet label like ATL-0240-FRZ in A2: =LEFT(A2,3) gives ATL, =RIGHT(A2,3) gives FRZ and =MID(A2,5,4) gives 0240.
What comes back is always text, even when it looks like a number. The 0240 that MID returns is not the number 240: SUM skips it, and a lookup against real numbers doesn't find it, until you convert it.
Syntax
=LEFT(text, [num_chars]) · =RIGHT(text, [num_chars]) · =MID(text, start_num, num_chars)
text- The text to take characters from: usually the cell that holds it.
num_chars- How many characters to return. Optional in LEFT and RIGHT (1 if you leave it out), required in MID. If it's more than the text has left, you get what there is, with no error.
start_num- MID only: the position of the first character to return. The first character of the text is position 1.
| A | B | C | |
|---|---|---|---|
| 1 | Label | Product | Warehouse |
| 2 | ATL-0240-FRZ | Chicken wings | ATL |
| 3 | DAL-0096-DRY | Rice, 50 lb | |
| 4 | ATL-0120-CHL | Greek yogurt | |
| 5 | PHX-0048-DRY | Olive oil | |
| 6 | DAL-0300-FRZ | French fries | |
| 7 | PHX-0180-CHL | Orange juice |
=RIGHT(A2,3) for the storage type, or to =MID(A2,5,4) for the units on the pallet.How Excel reads it
- Every character has a position, counted from 1. In
ATL-0240-FRZ, A is 1, the first hyphen is 4 and the 0 after it is 5. Hyphens and spaces count like any other character. LEFT(A2,3)returns positions 1 to 3.RIGHT(A2,3)returns the last 3, whatever the length of the text.MID(A2,5,4)starts at position 5 and returns 4 characters: 0240.
Ask for more characters than there are and you get what's left: =MID(A2,5,40) returns 0240-FRZ. Leave the number out of LEFT or RIGHT and you get one character.
The two ways it goes wrong
Both are live below. Fix the formula in the box and watch the cell.
1. MID starts one character off. The usual slip is counting the start from the wrong place, for example pointing at the hyphen instead of the first digit:
| A | B | C | |
|---|---|---|---|
| 1 | Label | Product | Units |
| 2 | ATL-0240-FRZ | Chicken wings | -024 |
| 3 | DAL-0096-DRY | Rice, 50 lb | |
| 4 | ATL-0120-CHL | Greek yogurt | |
| 5 | PHX-0048-DRY | Olive oil | |
| 6 | DAL-0300-FRZ | French fries | |
| 7 | PHX-0180-CHL | Orange juice |
-024. The digits start at position 5: change the 4 after A2 to 5.2. The digits you pulled out are text. In this sheet, column C was filled with =MID(A2,5,4). The cells look like numbers, but they sit on the left of the cell and keep their leading zeros: that's text. Adding them up gives nothing:
| A | B | C | |
|---|---|---|---|
| 1 | Label | Product | Units |
| 2 | ATL-0240-FRZ | Chicken wings | 0240 |
| 3 | DAL-0096-DRY | Rice, 50 lb | 0096 |
| 4 | ATL-0120-CHL | Greek yogurt | 0120 |
| 5 | PHX-0048-DRY | Olive oil | 0048 |
| 6 | DAL-0300-FRZ | French fries | 0300 |
| 7 | PHX-0180-CHL | Orange juice | 0180 |
| 8 | Total units | 0 |
=MID(A2,5,4)*1 returns a real number. To see the difference here, change the formula to =SUM(C2:C7*1): it converts each cell first and gives 984.The same thing breaks lookups. Excel treats the text 0240 and the number 240 as two different values, so an exact-match VLOOKUP for one doesn't find the other and returns #N/A. Multiplying by 1 makes Excel convert the text to a number; the VALUE function does the same: =VALUE(MID(A2,5,4)). (The sheets on this page don't run VALUE: use *1 in the exercises.)
When the code is an identifier, like a ZIP code or an ID with leading zeros, do the opposite: keep it as text on both sides of the lookup.
Practice: 3 exercises
Type the formula in the box, press Enter. Clicking a cell inserts its address. Each answer is checked on the data you see and again on changed data, so a typed-in value won't pass.
The first three letters of a label are the warehouse. In C2, return the warehouse code of the label in A2.
| A | B | C | |
|---|---|---|---|
| 1 | Label | Product | Warehouse |
| 2 | ATL-0240-FRZ | Chicken wings | |
| 3 | DAL-0096-DRY | Rice, 50 lb | |
| 4 | ATL-0120-CHL | Greek yogurt | |
| 5 | PHX-0048-DRY | Olive oil | |
| 6 | DAL-0300-FRZ | French fries | |
| 7 | PHX-0180-CHL | Orange juice |
Show the answer
=LEFT(A2,3) gives ATL.
LEFT counts from the first character. =MID(A2,1,3) returns the same thing.
The four digits in the middle are the units on the pallet. In C2, return them as a number (240, not the text 0240), so they can be added up.
| A | B | C | |
|---|---|---|---|
| 1 | Label | Product | Units |
| 2 | ATL-0240-FRZ | Chicken wings | |
| 3 | DAL-0096-DRY | Rice, 50 lb | |
| 4 | ATL-0120-CHL | Greek yogurt | |
| 5 | PHX-0048-DRY | Olive oil | |
| 6 | DAL-0300-FRZ | French fries | |
| 7 | PHX-0180-CHL | Orange juice |
Show the answer
=MID(A2,5,4)*1 gives 240.
MID returns text. Multiplying by 1 makes Excel convert it to a number without changing its value. In Excel, +0 or VALUE(…) do the same.
The warehouse codes are listed in E2:F4. In C2, return the city of the warehouse for the label in A2: pull the code out of the label and look it up.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Label | Product | City | Code | City | |
| 2 | ATL-0240-FRZ | Chicken wings | ATL | Atlanta | ||
| 3 | DAL-0096-DRY | Rice, 50 lb | DAL | Dallas | ||
| 4 | ATL-0120-CHL | Greek yogurt | PHX | Phoenix | ||
| 5 | PHX-0048-DRY | Olive oil | ||||
| 6 | DAL-0300-FRZ | French fries | ||||
| 7 | PHX-0180-CHL | Orange juice |
Show the answer
=VLOOKUP(LEFT(A2,3),E2:F4,2,FALSE) gives Atlanta.
LEFT returns the text ATL, and the Code column holds text too, so the two match. If the table's first column held numbers, the extracted text would need *1 first.
Questions people ask
How do I extract text before or after a character in Excel?
When every code has the same layout, count positions and use LEFT, MID or RIGHT with fixed numbers, as on this page. When the length varies, find the separator first: =LEFT(A2,FIND("-",A2)-1) returns everything before the first hyphen, because FIND gives the hyphen's position. Excel for Microsoft 365 and Excel 2024 also have TEXTBEFORE and TEXTAFTER, which take the separator directly. The sheets on this page don't run FIND or those two.
Why doesn't a number I extracted with MID work in SUM or VLOOKUP?
Because LEFT, RIGHT and MID return text, even when every character is a digit. SUM ignores text in a range, and an exact-match lookup treats the text 240 and the number 240 as different values. Convert the result where you extract it: =MID(A2,5,4)*1 or =VALUE(MID(A2,5,4)).
How do I remove the first characters of a text?
Start MID after them and ask for the rest. For ATL-0240-FRZ in A2, =MID(A2,5,LEN(A2)) returns 0240-FRZ: everything from position 5 on. =RIGHT(A2,LEN(A2)-4) gives the same result by counting from the other end.
Why does LEFT return strange digits from a date?
Excel stores a date as a serial number and only displays it as a date. LEFT works on the stored number: for March 14, 2026, which is serial 46095, =LEFT(A2,2) returns 46, not 03 or 3. Use YEAR, MONTH and DAY to take a date apart, or TEXT to turn it into text first.