LEFT, RIGHT and MID in Excel: how to use it
LEFT and RIGHT take characters from the start or the end of a text. MID takes a piece from the middle.
Try the first exercises for freeIn the lesson LEFT, RIGHT and MID
What do LEFT, RIGHT and MID do?
LEFT takes a number of characters from the start of a text, RIGHT a number of characters from the end. MID takes a piece from the middle: you say which character it starts at and how many characters you want.
That's how you cut fixed pieces out of codes, postcodes or numbers. The result is always text, even if you cut out digits. If you want to calculate with it, put VALUE around it.
When do you use LEFT, RIGHT and MID?
- The first part of a postcode such as LS1 4AP: =LEFT(A2,3).
- The last four digits of a card number: =RIGHT(A2,4).
- The year from an invoice number such as F-2026-0142: =MID(A2,3,4).
- The first letter of a first name, for initials: =LEFT(B2,1)&".".
Syntax
The syntax is the fixed pattern of a function. The words in the brackets are placeholders: that's where you fill something in yourself, such as a cell, a number or a piece of text. Everything else you type exactly as it is.
The first characters of a text.
Filled in: =LEFT(A2,3)
This is how to read the formula, part by part:
| Part | What it means | In the example |
|---|---|---|
= | Every formula starts with an equals sign. Without =, Excel treats what you type as ordinary text. | = |
LEFT | The name of the function: that's how Excel knows what to do. You can type it in lower case, Excel puts it in capitals itself. | LEFT |
( ) | Straight after the name come the brackets. Inside them is what the function needs to work: the arguments. | ( ) |
text | The cell with the text you want to cut a piece from. | A2 |
[num_chars] | Optional. How many characters you want. Leave it out with LEFT or RIGHT, and you get one character. | 3 |
, | A comma separates the arguments from each other. | , |
[ ] | Anything in square brackets can be left out. You never type the square brackets themselves. |
LEFT, RIGHT and MID step by step
C1 contains =LEFT(A2,3). The first three characters, the outward part of the postcode. Change the numbers or the formula and see what happens.
This is how to build the formula yourself:
- Click C1 and type LEFT with a bracket.
=LEFT( - Click the cell with the text, A2, and type a comma.
=LEFT(A2, - Type how many characters you want: the first part of this postcode is 3 characters long.
=LEFT(A2,3 - Close the bracket and press Enter: you get LS1, the first part of the postcode.
=LEFT(A2,3)
More examples
The last characters with RIGHT
C1 contains =RIGHT(A2,4). The last four characters of the card number. Spaces count as characters too.
A piece from the middle with MID
C1 contains =MID(A2,3,4). Start at the third character and take four: the year. The result is text.
Calculating with a number you cut out
C1 contains =VALUE(RIGHT(A2,2))*3. RIGHT gives the text 12. VALUE turns it into a number, so you can calculate with it: 3 boxes of 12 make 36 units.
The result is text
Even when you cut out digits, you get text. If you want to calculate with it, wrap it in VALUE.
Where it often goes wrong
- Spaces count as characters too.
- For pieces of varying length, combine with SEARCH or use TEXTBEFORE.
Practise this in the lesson LEFT, RIGHT and MID. The first exercises are free.
Frequently asked questions
How do I get a first name from a full name?
With =TEXTBEFORE(A2," ") in Excel 365, or with LEFT and SEARCH in older versions.