Excel LEFT, RIGHT and MID: Pull Part of a Text String Out
Take a fixed number of characters from the start, the end, or the middle of a text value.
These three functions do the same job from three directions. LEFT takes characters from the start of a text value, RIGHT takes them from the end, and MID takes them from a position you choose. Between them they cut apart product codes, extract area codes from phone numbers, and pull the year out of a reference number.
All three ask how many characters you want, and that is their limitation: they count positions, not meaning. LEFT(A2, 2) gives you the first two characters whether or not two characters is the right answer for that row. They work beautifully on data with a fixed shape — NW-2024-001 always has its prefix in the first two characters — and badly on anything ragged.
For ragged data, pair them with FIND to locate a delimiter and LEN to measure the string. That combination is the classic pre-2022 way of splitting text, and it is what TEXTSPLIT, TEXTBEFORE and TEXTAFTER replaced with something readable.
Syntax
=LEFT(text, [num_chars]) =RIGHT(text, [num_chars]) =MID(text, start_num, num_chars)Arguments
textRequired- The text value to take characters from. All three share this argument.
num_charsOptional- How many characters to take. LEFT and RIGHT default to 1 if you leave it out. Asking for more characters than exist returns the whole string rather than erroring.
start_numRequired- MID only: which character to start at, counting from 1. MID(A2, 4, 4) starts at the fourth character.
The example data
Headers in row 1, data in A2:C5. Note the stray spaces in A3 and A5.
| A | B | C | |
|---|---|---|---|
| 1 | Name | Code | |
| 2 | Alice Moreau | alice@northwind.com | NW-2024-001 |
| 3 | bruno santos | bruno@southgate.co.uk | SG-2024-014 |
| 4 | CHEN WEI | chen@northwind.com | NW-2023-207 |
| 5 | Dana Okafor | dana@eastvale.org | EV-2024-092 |
Worked examples
=LEFT(C2, 2)Result: NW
The first two characters of the code — the account prefix.
=RIGHT(C2, 3)Result: 001
The last three characters, the sequence number. Note it stays text, not a number.
=MID(C2, 4, 4)Result: 2024
Four characters starting at position 4 — the year, which sits between two hyphens.
=LEFT(B2, FIND("@", B2) - 1)Result: alice
The ragged case. FIND locates the @, and LEFT takes everything before it. The -1 is what stops the @ coming along too.
Now practise it
Reading about a formula is not the same as writing one. Open this function's exercise and type it into a real grid — you get instant feedback on exactly which cell is wrong and why.
Common errors and how to fix them
#VALUE!
Why it happens: num_chars or start_num is negative, or MID was given a start position below 1.
How to fix it: This usually comes from a FIND that failed, returning #VALUE! into the arithmetic. Check the delimiter you are searching for actually appears.
Empty result from MID
Why it happens: start_num is past the end of the string, so there is nothing to return.
How to fix it: Guard it with LEN: only run the MID when LEN(text) is long enough.
Result looks like a number but will not add up
Why it happens: All three always return text. RIGHT(C2, 3) gives the characters "001", not the number 1.
How to fix it: Wrap it in VALUE to convert: =VALUE(RIGHT(C2, 3)).
Leading spaces come through
Why it happens: LEFT counts the space as a character, because it is one.
How to fix it: Clean first: =LEFT(TRIM(A3), 5).
Tips worth knowing
- LEFT and RIGHT default to one character when you omit num_chars, which is occasionally what you want and more often a bug.
- Asking for more characters than the string holds is safe — you get the whole string, not an error.
- =RIGHT(A2, LEN(A2) - FIND("@", A2)) is the mirror of the LEFT+FIND pattern, taking everything after a delimiter.
- If your Excel has TEXTBEFORE and TEXTAFTER, use those instead — same result, and you can read it back in six months.
Frequently asked questions
How do I extract text before a specific character?
Use LEFT with FIND: =LEFT(A2, FIND("@", A2) - 1). FIND returns the position of the character and the -1 excludes it. In Microsoft 365, TEXTBEFORE(A2, "@") does the same thing far more legibly.
What is the difference between MID and TEXTSPLIT?
MID takes one chunk at a fixed position that you have to work out yourself. TEXTSPLIT breaks the whole string on a delimiter and returns every piece at once. If your data has a consistent separator, TEXTSPLIT is almost always the better tool.
Why does my extracted number not sum?
LEFT, RIGHT and MID always return text, even when every character is a digit. Wrap the result in VALUE() to turn it into a real number, or multiply by 1.
Related functions
Guides that use it
Longer reads where this function does real work in a real sheet.