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.

Skip to the 3 exercises ↓

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.
Live exampleOne part of a label
C2fx
ABC
1LabelProductWarehouse
2ATL-0240-FRZChicken wingsATL
3DAL-0096-DRYRice, 50 lb
4ATL-0120-CHLGreek yogurt
5PHX-0048-DRYOlive oil
6DAL-0300-FRZFrench fries
7PHX-0180-CHLOrange juice

How Excel reads it

  1. 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.
  2. LEFT(A2,3) returns positions 1 to 3. RIGHT(A2,3) returns the last 3, whatever the length of the text.
  3. 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:

Live exampleMID that starts on the hyphen
C2fx
ABC
1LabelProductUnits
2ATL-0240-FRZChicken wings-024
3DAL-0096-DRYRice, 50 lb
4ATL-0120-CHLGreek yogurt
5PHX-0048-DRYOlive oil
6DAL-0300-FRZFrench fries
7PHX-0180-CHLOrange juice

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:

Live exampleSUM of extracted digits: 0
C8fx
ABC
1LabelProductUnits
2ATL-0240-FRZChicken wings0240
3DAL-0096-DRYRice, 50 lb0096
4ATL-0120-CHLGreek yogurt0120
5PHX-0048-DRYOlive oil0048
6DAL-0300-FRZFrench fries0300
7PHX-0180-CHLOrange juice0180
8Total units0

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.

Exercise 1Warehouse code

The first three letters of a label are the warehouse. In C2, return the warehouse code of the label in A2.

C2fx
ABC
1LabelProductWarehouse
2ATL-0240-FRZChicken wings
3DAL-0096-DRYRice, 50 lb
4ATL-0120-CHLGreek yogurt
5PHX-0048-DRYOlive oil
6DAL-0300-FRZFrench fries
7PHX-0180-CHLOrange juice
Show the answer

=LEFT(A2,3) gives ATL.

LEFT counts from the first character. =MID(A2,1,3) returns the same thing.

Exercise 2Units as a real number

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.

C2fx
ABC
1LabelProductUnits
2ATL-0240-FRZChicken wings
3DAL-0096-DRYRice, 50 lb
4ATL-0120-CHLGreek yogurt
5PHX-0048-DRYOlive oil
6DAL-0300-FRZFrench fries
7PHX-0180-CHLOrange 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.

Exercise 3Look up the city for a label

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.

C2fx
ABCDEF
1LabelProductCityCodeCity
2ATL-0240-FRZChicken wingsATLAtlanta
3DAL-0096-DRYRice, 50 lbDALDallas
4ATL-0120-CHLGreek yogurtPHXPhoenix
5PHX-0048-DRYOlive oil
6DAL-0300-FRZFrench fries
7PHX-0180-CHLOrange 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.

Keep going

SheetDrill has no lesson on text functions yet. The free skills test is ten questions in a real spreadsheet, from the basics to INDEX/MATCH, and it ends by pointing you to the lesson to start with. No sign-up.

Related

TRIM function, with live examplesCONCAT function and the & operator, with live examplesTEXT function, with live examplesVLOOKUP function, with live examples#N/A error in Excel: meaning and fixesExcel functions with live examplesExcel practice online

Written and tested by Nico, who builds SheetDrill (about). Every formula on this page ran through the engine that checks the exercises before the page was published. Checked against: Microsoft Support: LEFT function, RIGHT function, MID function, VALUE function, How to correct a #N/A error, How to correct a #VALUE! error in FIND and SEARCH, TEXTBEFORE function. Last checked November 2026.