Excel LEFT, RIGHT, and MID Functions
last modified October 5, 2026
The LEFT, RIGHT, and MID functions are
text functions that extract a substring from a larger string.
LEFT takes characters from the beginning, RIGHT from
the end, and MID from any position in between. They are the core
tools for parsing structured values such as file names, phone numbers, product
codes, and full names. This tutorial covers their syntax, the exact rules for
each argument, practical examples, and the most common pitfalls.
Function Basics
All three functions count characters from the left, starting at position 1.
They always return text, even when the source value is a number. The number of
characters to return is called num_chars.
| Function | Description | Syntax |
|---|---|---|
| LEFT | Extracts characters from the start of the text | =LEFT(text, [num_chars]) |
| RIGHT | Extracts characters from the end of the text | =RIGHT(text, [num_chars]) |
| MID | Extracts characters from any position | =MID(text, start_num, num_chars) |
This table summarizes the three functions. Optional arguments are shown in
brackets. Only MID has no optional arguments.
| Argument | Meaning |
|---|---|
| text | The source string. It can be a literal, a cell reference, or an
expression such as A1&"-"&B1. Numbers are converted to
text automatically. |
| start_num | Used by MID only. The 1-based position of the first
character to return. It must be 1 or greater. |
| num_chars | The number of characters to return. It must be 0 or greater. For
LEFT and RIGHT it defaults to 1; for
MID it is required. |
The following rules apply to every function in this tutorial. If
num_chars is greater than the remaining text, all remaining
characters are returned. If num_chars is 0, the result is an empty
string. A negative num_chars, or a start_num less
than 1, produces a #VALUE! error.
LEFT Function
LEFT returns characters from the beginning of a string. This
example extracts the first three characters.
=LEFT("Excel Functions", 3)
The result is Exc. When num_chars is omitted,
LEFT returns a single character, so
=LEFT("Excel Functions") returns E. If the count is
larger than the text, the whole string is returned:
=LEFT("Excel", 100) returns Excel.
The text argument can be a number. Because the result is always
text, =LEFT(12345, 2) returns 12 as a text value, not
as the number 12.
RIGHT Function
RIGHT returns characters from the end of a string. This example
extracts the last nine characters.
=RIGHT("Excel Functions", 9)
The result is Functions. As with LEFT,
num_chars is optional and defaults to 1, and a count larger than
the text returns the entire string.
MID Function
MID returns characters starting at start_num. Unlike
the other two functions, both start_num and
num_chars are required.
=MID("Excel Functions", 7, 9)
The result is Functions because the character at position 7 is
F. If start_num is greater than the length of the
text, MID returns an empty string rather than an error. If
start_num plus num_chars reaches past the end, all
characters up to the end are returned.
| Formula | Result | Reason |
|---|---|---|
| =MID("Excel", 3, 2) | ce | Two characters from position 3 |
| =MID("Excel", 3, 100) | cel | Count runs past the end, so the rest is returned |
| =MID("Excel", 10, 2) | (empty) | start_num is past the end of the text |
| =MID("Excel", 0, 2) | #VALUE! | start_num must be 1 or greater |
These rows show the boundary behavior of MID. The empty result in
the third row is easy to mistake for an error, so test for it with
=IF(MID(A1,10,2)="", "missing", MID(A1,10,2)) when the position
may not exist.
Edge Cases for num_chars
The count argument behaves the same way in all three functions. The table below lists the values you are most likely to meet.
| num_chars | Result |
|---|---|
| 0 | Empty string |
| Positive and within the text | That many characters |
| Larger than the text | The entire text |
| Negative | #VALUE! error |
| Omitted in LEFT or RIGHT | 1 character |
| Omitted in MID | Not allowed; MID requires all three arguments |
A common trick is to pass a very large number such as 100 or 255 to capture everything from a position to the end. This is clearer than computing the exact length and works in every version of Excel.
Extracting File Extensions with RIGHT
RIGHT is well suited to pulling the extension from a file name.
This example returns the last three characters, which works for three-letter
extensions such as .txt and .pdf.
| A | B |
|---|---|
| document.txt | =RIGHT(A1, 3) |
| report.pdf | =RIGHT(A2, 3) |
Cell B1 returns txt and B2 returns pdf. The approach
assumes a fixed three-character extension. For variable-length extensions, use
FIND to locate the dot and feed the distance into
RIGHT.
=RIGHT(A1, LEN(A1)-FIND(".", A1))
For document.txt, FIND returns 9, the position of the
dot, so RIGHT returns the last LEN(A1)-9 characters,
which is txt. The same form handles names such as
archive.xlsx, where it returns xlsx.
Extracting Middle Names with MID
MID extracts text from any position. This example gets the middle
name from full names in a fixed first-middle-last format.
| A | B |
|---|---|
| John Michael Doe | =MID(A1, 6, 7) |
| Jane Ann Smith | =MID(A2, 6, 3) |
The first space is at position 5, so the middle name starts at position 6. B1
returns Michael and B2 returns Ann. The formulas rely
on a fixed first-name length; the next section shows how to make the position
dynamic.
Combining LEFT and FIND to Extract First Names
Combining LEFT with FIND makes the extraction
independent of the name length. FIND locates the first space and
LEFT returns everything before it.
=LEFT(A1, FIND(" ", A1)-1)
For Sarah Johnson in A1, FIND returns 6, so
LEFT returns 5 characters: Sarah. Subtracting 1
removes the space itself. If there is no space, FIND returns
#VALUE!; wrap the formula in IFERROR to handle
single-word names.
Extracting Area Codes with MID
This example uses MID to pull the three-digit area code out of a
formatted phone number.
| A | B |
|---|---|
| (555) 123-4567 | =MID(A1, 2, 3) |
| (800) 555-1212 | =MID(A2, 2, 3) |
The opening parenthesis occupies position 1, so the area code begins at
position 2. B1 returns 555 and B2 returns 800. The
formula assumes the standard (nnn) nnn-nnnn layout.
Extracting Text Between Delimiters
MID and FIND work together to extract the text
between two characters. The next formula returns whatever sits inside a pair of
parentheses.
=MID(A1, FIND("(", A1)+1, FIND(")", A1)-FIND("(", A1)-1)
For (example) the opening parenthesis is at 1 and the closing one
at 9, so MID starts at 2 and returns 7 characters:
example. SEARCH can be substituted for
FIND when the delimiters are letters whose case should be ignored.
Converting Results to Numbers with VALUE
Because these functions always return text, a number pulled from a string does
not behave like a number until it is converted. The VALUE function
performs that conversion.
| A | B | C |
|---|---|---|
| Order 1234 | =RIGHT(A1, 4) | =VALUE(RIGHT(A1, 4)) |
B1 returns the text 1234, which is left-aligned. C1 returns the
number 1234, which is right-aligned and can be used in arithmetic. A shorter
alternative is to add zero, as in =RIGHT(A1, 4)+0. Use
VALUE when the extracted digits may include a currency symbol or a
thousands separator that must be interpreted.
Numbers and Dates Use the Underlying Value
LEFT, RIGHT, and MID convert a numeric
cell to text using its underlying value, not the way it is displayed. A date
such as 1/15/2023 is stored as the serial number 44941, so
=LEFT(A1, 2) returns 44, not 1/.
=LEFT(TEXT(A1, "mm/dd/yyyy"), 2)
TEXT first renders the date in the requested format, and
LEFT then returns 01. Apply the same idea to numbers
with a custom format when the display, rather than the stored value, is what
you want to parse.
Handling Variable-Length Text with RIGHT
To grab the last word of a sentence, first find the position of the last space.
The formula replaces the final space with an asterisk, finds it, and returns
everything after it with RIGHT.
=RIGHT(A1, LEN(A1)-FIND("*", SUBSTITUTE(A1, " ", "*", LEN(A1)-LEN(SUBSTITUTE(A1, " ", "")))))
For The quick brown fox, the string contains three spaces, so
SUBSTITUTE replaces the third one with *. That
asterisk lands at position 16, and RIGHT returns the last
19-16 characters: fox.
LEFT, RIGHT, and MID Errors and Troubleshooting
| Cause | Symptom | Fix |
|---|---|---|
| num_chars is negative | #VALUE! error | Use 0 or a positive count; never a negative number |
| start_num is less than 1 in MID | #VALUE! error | Remember that positions start at 1, not 0 |
| start_num is past the end of the text | Empty text, not an error | Test with LEN before calling MID, or wrap in IF |
| num_chars omitted in MID | Formula is rejected | MID requires text, start_num, and num_chars |
| Nested FIND or SEARCH does not match | #VALUE! error | Wrap the nested function in IFERROR or test with ISNUMBER |
| Extracted number used in a calculation | Unexpected result or left-aligned text | Convert with VALUE or add 0 |
| Date or number extracted as displayed | Serial digits instead of the formatted text | Apply TEXT before extracting |
The LEFT, RIGHT, and MID functions are
the foundation of text parsing in Excel. LEFT and
RIGHT pull characters from the two ends, and MID
reaches any position in between. Remember that all three return text, that
num_chars must be zero or greater, and that MID needs
a start_num of at least 1. Combined with FIND,
LEN, SUBSTITUTE, TEXT, and
VALUE, they let you split, clean, and convert almost any
structured string.
Author
List all Excel Formulas.