LEFT, RIGHT and MID in Excel
Last updated
LEFT, RIGHT and MID take part of a text. =LEFT(A2,2) returns the first 2 characters of A2, =RIGHT(A2,3) the last 3, and =MID(A2,5,4) 4 characters starting at the 5th. When the length changes from row to row, work it out with FIND and LEN.
LEFT, RIGHT and MID syntax
=LEFT(text, [num_chars]) =RIGHT(text, [num_chars]) =MID(text, start_num, num_chars)
- text
- The text to cut, usually a cell such as
A2. A number works too and is treated as text. - num_chars
- How many characters to keep. Optional for LEFT and RIGHT, where it defaults to 1; required for MID. If it is larger than the text, you get the whole text.
- start_num
- MID only. The position of the first character to take: 1 is the first character of the text. Spaces and hyphens count.
- LEN and FIND
LEN(A2)returns the number of characters in A2.FIND(" ",A2)returns the position of the first space, so you can cut at a separator wherever it is. SEARCH works like FIND but ignores case.
Practice LEFT, RIGHT and MID here
Type your formula straight into the sheet. It’s checked like real Excel, and you can write it with commas or semicolons.
Take the country code from an order ID
In C2, use LEFT to take the first two characters of the order ID in A2, to get the country code US.
| Order ID | Customer | Country code |
| US-20431 | Northwind Foods | |
| CA-20432 | Bluebird Studio | |
| GB-20433 | Harbor Logistics | |
| AU-20434 | Maple Dental | |
| US-20435 | Cedar Books |
How to use LEFT, RIGHT and MID, step by step
- Click the cell for the result and type
=LEFT(,=RIGHT(or=MID(. - Click the cell that holds the text, then type a comma.
- For MID, type the position of the first character to take, then a comma. Count every character, including spaces and hyphens.
- Type how many characters to keep, or a FIND formula when the length varies, close the bracket and press Enter.
LEFT, RIGHT and MID examples
Country code from an order ID
=LEFT(A2,2)
Returns US from US-20431. Every ID starts with a two-letter code, so the same formula works on every row.
Colour code from the end
=RIGHT(A2,3)
Returns BLK from LAP-2041-BLK: the last three characters.
Model number from the middle
=MID(A2,5,4)
Returns 2041 from LAP-2041-BLK: 4 characters starting at the 5th, just after the hyphen. The result is the text "2041", not a number.
First name of any length
=LEFT(A2,FIND(" ",A2)-1)
Returns Olivia from Olivia Reed. FIND locates the space (character 7), and LEFT keeps everything before it. For the last name, use =RIGHT(A2,LEN(A2)-FIND(" ",A2)).
Common problems and how to fix them
| Result | Cause | Fix |
|---|---|---|
| #VALUE! | FIND or SEARCH didn’t find the separator, for example a name with no space, or a negative number of characters. | Check that every row contains the separator, or wrap the formula in IFERROR to show your own text instead. |
| One character too many | The result keeps the space or the hyphen, as in "Olivia " or "2041-". | Subtract 1 from the position FIND returns, or start MID one character later with FIND(" ",A2)+1. |
| The result doesn’t add up | LEFT, RIGHT and MID always return text, so "2041" is aligned left and ignored by SUM. | Convert it with VALUE: =VALUE(MID(A2,5,4)), or put two minus signs in front: --MID(A2,5,4). |
| Wrong part on some rows | A fixed number of characters only works when every value has the same length, so longer names get cut. | Replace the number with FIND or LEN so each row works out its own length. |
LEFT vs RIGHT vs MID, and when to add FIND
Use LEFT when the part you need is at the start, RIGHT when it is at the end and MID when it sits in the middle. With fixed-length codes a plain number is enough; when lengths vary, combine them with FIND (case-sensitive) or SEARCH (ignores case and accepts wildcards) and LEN. Newer versions of Excel add TEXTBEFORE, TEXTAFTER and TEXTSPLIT, which cut at a separator directly. LEFT, RIGHT and MID work in every version, and this practice grid supports LEFT, RIGHT, MID, LEN, FIND and SEARCH, not the newer functions.
Frequently asked questions
How do I take the first word of a cell?
Cut just before the first space: =LEFT(A2,FIND(" ",A2)-1).
How do I take everything after a character?
Use RIGHT with the length minus the position of the character: =RIGHT(A2,LEN(A2)-FIND("-",A2)), or MID from the next position: =MID(A2,FIND("-",A2)+1,100).
Why can’t I add the numbers MID returns?
MID, LEFT and RIGHT always return text. Wrap the formula in VALUE to turn "2041" into the number 2041.
What is the difference between FIND and SEARCH?
FIND is case-sensitive and doesn’t accept wildcards; SEARCH ignores case and accepts * and ?. To find a space or a hyphen, both give the same result.
Keep practising
All 5 LEFT, RIGHT and MID exercises, with explanations and progress tracking.
Open the LEFT, RIGHT and MID practice set