Text
How to Use LEFT, RIGHT, and MID in Excel (Examples)
LEFT, RIGHT, and MID are the three functions for pulling pieces out of text. LEFT takes characters from the start, RIGHT takes them from the end, and MID grabs a chunk from the middle starting at a position you choose.
They are the building blocks for splitting names, extracting codes, and cleaning up imported data. Paired with FIND or SEARCH to locate a separator, they can carve up almost any string. This guide shows the syntax and the most common patterns.
LEFT, RIGHT, MID syntax
=LEFT(text, [num_chars]) =RIGHT(text, [num_chars]) =MID(text, start_num, num_chars) | Argument | Required | What it does |
|---|---|---|
text | Required | The text or cell you are extracting from. Used by all three functions. |
num_chars (LEFT/RIGHT) | Optional | How many characters to take from the start (LEFT) or end (RIGHT). Optional; defaults to 1. |
start_num (MID) | Required | For MID: the position of the first character to extract, counting from 1. |
num_chars (MID) | Required | For MID: how many characters to return, starting at start_num. |
Note: These count characters, not words. To find a separator position automatically, nest FIND or SEARCH inside them. All three work in Google Sheets.
Examples
Real, copy-paste-ready formulas.
1. Get the first name from a full name
=LEFT(A2, FIND(" ", A2)-1) FIND locates the space, and LEFT takes everything before it. The -1 stops just before the space so the name has no trailing blank.
2. Get the last 4 characters
=RIGHT(A2, 4) RIGHT counts 4 characters in from the end. Handy for showing only the last digits of an ID or account number.
3. Extract a middle section
=MID(A2, 4, 3) MID starts at character 4 and returns the next 3 characters. Perfect for codes where a segment sits at a fixed position.
4. Get the last name from a full name
=MID(A2, FIND(" ", A2)+1, LEN(A2)) FIND locates the space; MID starts one character after it and takes the rest. LEN(A2) is a safe upper bound for how many characters remain.
How to write LEFT, RIGHT, MID step by step
- 1
Decide which end you need: LEFT for the start, RIGHT for the end, MID for the middle.
- 2
Type =LEFT( (or =RIGHT( / =MID() and click the cell holding the text, then a comma.
- 3
For LEFT or RIGHT, type how many characters to take and close the bracket ).
- 4
For MID, type the start position, a comma, then how many characters to take, and close the bracket ).
- 5
To find a separator automatically, replace the number with FIND(" ", A2), e.g. =LEFT(A2, FIND(" ", A2)-1).
- 6
Press Enter and fill the formula down.
Common errors and fixes
| Error | Why it happens | How to fix it |
|---|---|---|
#VALUE! | num_chars or start_num is negative, or a nested FIND failed to locate its target. | Use positive whole numbers, and wrap a nested FIND in IFERROR so a missing separator does not break the formula. |
Extra space in the result | The character count included the separator itself. | Subtract 1 when extracting up to a space, e.g. LEFT(A2, FIND(" ", A2)-1). |
Result is too short or too long | A fixed character count does not fit every row because the text varies in length. | Use FIND or SEARCH to locate the split point dynamically instead of a hard-coded number. |
Leading zeros disappear | The source was a number, so Excel dropped the zeros before LEFT/RIGHT/MID ran. | Store such codes as text, or wrap the value in TEXT() first to fix its width. |
Frequently asked questions
What is the difference between LEFT, RIGHT, and MID?
LEFT returns characters from the start of the text, RIGHT returns them from the end, and MID returns a chunk from a position you choose in the middle. They differ only in where they begin counting.
How do I extract the first name from a full name?
Use =LEFT(A2, FIND(" ", A2)-1). FIND locates the space between first and last name, and LEFT takes everything before it.
How do I get text after a specific character?
Combine MID with FIND, e.g. =MID(A2, FIND("-", A2)+1, LEN(A2)) returns everything after the first dash.
Do LEFT, RIGHT, and MID work in Google Sheets?
Yes. All three have identical syntax in Google Sheets, and you can nest FIND or SEARCH inside them the same way.