The LEFT and RIGHT functions in Excel
LEFT takes a number of characters from the start of a text; RIGHT takes them from the end. They're the quickest way to split codes with a fixed layout, such as a two-letter category at the start or a color code at the end.
When the part you want has a variable length, combine them with FIND to cut at a space, a dash or an @.
Syntax
=LEFT(text, [num_chars])
| Argument | What it means |
|---|---|
| text | The text or cell to take characters from. |
| num_charsoptional | How many characters to take. Default 1. RIGHT has the same arguments. |
Examples
1. The first characters
| A | B | C | D | |
|---|---|---|---|---|
| 1 | SKU | Category | ||
| 2 | SH-4417-BLU | omar.haddad@shopmail.com | SH |
The first 2 characters of SH-4417-BLU.
2. The last characters: RIGHT
| A | B | C | D | |
|---|---|---|---|---|
| 1 | SKU | Color | ||
| 2 | SH-4417-BLU | omar.haddad@shopmail.com | BLU |
The last 3 characters. When the characters you take are digits, the result is still text: wrap it in VALUE to calculate with it.
3. Cut at a space: the first name
| A | B | |
|---|---|---|
| 1 | Full name | First name |
| 2 | Maya Cohen | Maya |
FIND returns the position of the space (5); everything before it is 4 characters.
Common mistakes
Expecting a number.
=RIGHT(A2,4)on a code returns "2026" as text, which won't add up in SUM.Fix: Wrap it in VALUE, or multiply by 1.
Hidden spaces at the start of the text shift everything by one.
Fix: Clean the text with TRIM first:
=LEFT(TRIM(A2),2).
Frequently asked questions
How do I get characters from the middle?
Use MID, which takes a start position and a length.
Is there an easier way to split text at a character?
In Excel 365, TEXTBEFORE and TEXTAFTER split at a delimiter without counting positions.
Related functions
- MIDMID returns characters from the middle of a text: a start position and a length.
- FINDFIND and SEARCH return the position of one text inside another.
- TEXTAFTERTEXTAFTER and TEXTBEFORE split text at a delimiter, such as the @ in an e-mail or a dash in a code, without counting positions.
- LENLEN returns the number of characters in a text, spaces included.