AaText14 min read ·

How to Use LEFT, RIGHT and MID in Excel

Learn LEFT, RIGHT and MID with real examples. Get text from the start, end or middle of a cell, cut it at a character with FIND and LEN, and fix #VALUE!.

Quick answer

=LEFT(text, [num_chars])

LEFT returns the first characters of a text, RIGHT the last ones, and MID the characters from a position you choose, like =LEFT(D2, 2) for the first two. To cut at a character such as @, find it with FIND, as in =LEFT(B2, FIND("@", B2)-1).

Arguments in [brackets] are optional.

On this page ⌄
  1. The LEFT, RIGHT and MID syntax
  2. Get the first characters with LEFT
  3. Get the last characters with RIGHT
  4. Get characters from the middle with MID
  5. Count characters with LEN
  6. Find a character's position with FIND or SEARCH
  7. Extract text before a character
  8. Extract text after a character
  9. Extract text between two characters
  10. LEFT, RIGHT and MID return text, not numbers
  11. Too many characters, zero and negative numbers
  12. TEXTBEFORE and TEXTAFTER in Microsoft 365
  13. Common LEFT, RIGHT and MID mistakes
  14. Frequently asked questions about LEFT, RIGHT and MID
  15. Which versions of Excel have LEFT, RIGHT and MID?
  16. Key takeaways

LEFT, RIGHT and MID pull part of a text out of a cell. LEFT takes characters from the start, RIGHT takes them from the end, and MID takes them from any position in between. They're how you get a store code out of an order number, the last four digits of a phone number or the username out of an email address:

=LEFT(text, [num_chars])
=RIGHT(text, [num_chars])
=MID(text, start_num, num_chars)

On their own, they cut a fixed number of characters. Combined with FIND and LEN, they can cut at a character instead, such as the @ in an email, so the same formula works when every cell has a different length. All three work in every version of Excel.

The examples use this customer export in A1:D6:

A: Full nameB: EmailC: PhoneD: Order code
2emma JOHNSON[email protected](555) 201-4433NW-2026-0187
3liam o'brien[email protected]555.388.1902CT-2025-0942
4OLIVIA chen[email protected]555-647-2210FB-2026-0033
5noah patel[email protected](555) 903 7751NW-2026-0451
6Sofia Martinez[email protected]555 412 0098AW-2024-1120

Each order code is a 2-letter store code, the year and a 4-digit order number, joined by dashes. The names are messy on purpose. A2 has spaces before, between and after the words, A4 ends with two spaces, and A5 has three spaces in the middle. You can't see them in the table, but Excel counts every one, which matters later.

The LEFT, RIGHT and MID syntax

ArgumentWhat it meansExample
textThe text to take characters from, usually a cellD2
num_charsHow many characters to return. Optional in LEFT and RIGHT, where leaving it out means 1. Required in MID2
start_numMID only. The position of the first character to return, where the first character is 14

Every character counts, including spaces, dashes, dots and brackets. In NW-2026-0187, the first dash is character 3, so the year starts at character 4.

Get the first characters with LEFT

Which store placed the first order? The store code is the first two characters of D2:

=LEFT(D2, 2)→ NW

Ask for more characters to get more. The first seven characters of an order code are the store and the year:

=LEFT(D5, 7)→ NW-2026

Leave out num_chars and LEFT returns one character. That's a quick way to get an initial from a tidy name:

=LEFT(A6)→ S

Your turn. The sheet below is live and uses the same customer export. The exercise asks for the store code of a different order: point LEFT at the right cell, tell it how many characters to take, and press Enter.

Try it yourself

A live spreadsheet. Type a formula and press Enter to check it.

Exercise 2 of 11LEFT

Get the 2-letter store code from the start of the order code in D3.

Write your formula in the green cell, G2.

customer-export.xlsx
fx
1Full nameEmailPhoneOrder code
2 emma JOHNSON [email protected](555) 201-4433NW-2026-0187Store
3liam o'brien[email protected]555.388.1902CT-2025-0942
4OLIVIA chen [email protected]555-647-2210FB-2026-0033
5noah patel[email protected](555) 903 7751NW-2026-0451
6Sofia Martinez[email protected]555 412 0098AW-2024-1120
7
8
9
10

Click a cell to add it to your formula, drag to select a range, or click a column letter for the whole column.

Get the last characters with RIGHT

RIGHT works the same way, from the end. The last four characters of an order code are the order number:

=RIGHT(D2, 4)→ 0187

The result keeps its leading 0, because LEFT, RIGHT and MID always return text. More on that below.

RIGHT is handy when the end of the text is consistent but the start isn't. The phone numbers in column C are written in five different styles, with brackets, dots, dashes and spaces, but every one ends with four digits. So RIGHT gets the last four digits from all of them:

=RIGHT(C2, 4)→ 4433
=RIGHT(C3, 4)→ 1902
=RIGHT(C6, 4)→ 0098

LEFT is less forgiving here. The area code is the first three digits, but two of the numbers start with a bracket:

=LEFT(C3, 3)→ 555
=LEFT(C5, 3)→ (55

LEFT counts characters, not digits, so the bracket takes the first spot. MID fixes that, as shown next.

Get characters from the middle with MID

MID needs a start position as well as a length. Which year was order AW-2024-1120 placed? Here are the characters in D6 and their positions:

Position123456789101112
D6AW-2024-1120

The year starts at position 4 and is 4 characters long:

=MID(D6, 4, 4)→ 2024

MID also fixes the phone number that starts with a bracket. Start at position 2, just after the bracket, and take three characters:

=MID(C5, 2, 3)→ 555

To take everything from a position to the end, give MID a num_chars at least as long as the text. MID stops at the end of the text, so a big number is safe:

=MID(D6, 4, 100)→ 2024-1120

=MID(D6, 4, LEN(D6)) does the same thing without guessing a number. LEN is next.

Count characters with LEN

LEN returns the number of characters in a cell, spaces included:

=LEN(D2)→ 12
=LEN(B2)→ 26
=LEN(A2)→ 17

The name in A2 has 11 letters, but LEN counts 17, because of the six spaces before, between and after the words. TRIM removes the extras, so =LEN(TRIM(A2)) returns 12: the 11 letters of emma JOHNSON and one space between them.

LEN is what lets RIGHT and LEFT work on text of any length. To remove characters from the start, ask RIGHT for the length minus that many. To remove them from the end, do the same with LEFT. These drop the store code and its dash, then the dash and order number:

=RIGHT(D2, LEN(D2)-3)→ 2026-0187
=LEFT(D2, LEN(D2)-5)→ NW-2026

Fixed positions work for the order codes, because every code has the same layout. Emails and names don't: the @ in B2 is the 13th character, but in B4 it's the 7th. FIND returns the position of one piece of text inside another:

=FIND("@", B2)→ 13
=FIND("@", B4)→ 7

SEARCH does the same job. The differences:

FINDSEARCH
Upper and lower caseMust matchIgnored
Wildcards, * and ?Not supportedSupported
When the text isn't there#VALUE!#VALUE!

The name in A4 is written in capitals, so only SEARCH finds it in lowercase:

=FIND("olivia", A4)→ #VALUE!
=SEARCH("olivia", A4)→ 1

For symbols like @, a space or a dash, either one works. For letters, SEARCH is usually safer, unless the case matters. Both take an optional third argument, start_num, the position to start looking from, which you'll need to find the second of something.

Extract text before a character

To get everything before the @, find the @ and take one character fewer with LEFT:

=LEFT(B2, FIND("@", B2)-1)→ emma.johnson

The @ is the 13th character, so LEFT takes the first 12. Without the -1, you'd get emma.johnson@ with the @ on the end. Because FIND works out the position for each row, the same formula copied down handles every length:

B: EmailPosition of @Username
2[email protected]13emma.johnson
3[email protected]12liam.obrien
4[email protected]7o.chen
5[email protected]7noah.p
6[email protected]8sofia.m

The first name is the text before the first space, so the same pattern works for names:

=LEFT(A6, FIND(" ", A6)-1)→ Sofia

Extra spaces throw this off. A2 starts with a space, so FIND returns 1, LEFT takes 0 characters, and =LEFT(A2, FIND(" ", A2)-1) returns empty text. Clean the name with TRIM first, in both places, and add PROPER to fix the capitals:

=LEFT(TRIM(A2), FIND(" ", TRIM(A2))-1)→ emma
=PROPER(LEFT(TRIM(A2), FIND(" ", TRIM(A2))-1))→ Emma

The free clean-name exercise practices TRIM and PROPER on the same names.

If the character isn't in the cell, FIND returns #VALUE!, and so does the formula around it. Every email has an @, but only three of the five phone numbers have a space:

=LEFT(C2, FIND(" ", C2)-1)→ (555)
=LEFT(C3, FIND(" ", C3)-1)→ #VALUE!
=IFERROR(LEFT(C3, FIND(" ", C3)-1), C3)→ 555.388.1902

IFERROR shows the whole cell instead of the error. Use it only where a missing character is expected, so other mistakes still show.

Extract text after a character

For the text after the @, you need to know how many characters follow it. That's the length of the cell minus the position of the @:

=RIGHT(B2, LEN(B2)-FIND("@", B2))→ northwind.com

B2 has 26 characters and the @ is the 13th, so RIGHT takes the last 13. MID gives the same answer. Start one character after the @, and ask for as many characters as the cell has, so it runs to the end:

=MID(B2, FIND("@", B2)+1, LEN(B2))→ northwind.com

The last name works the same way, with a space instead of the @:

=RIGHT(A6, LEN(A6)-FIND(" ", A6))→ Martinez

Extract text between two characters

For text in the middle, find both ends. The company name in B2 sits between the @ and the dot. The catch is that B2 has two dots, one in emma.johnson and one in northwind.com. FIND's third argument tells it to start looking at the @, so it skips the first dot:

=FIND(".", B2)→ 5
=FIND(".", B2, FIND("@", B2))→ 23
=MID(B2, FIND("@", B2)+1, FIND(".", B2, FIND("@", B2))-FIND("@", B2)-1)→ northwind

MID starts at position 14, just after the @, and takes 23 − 13 − 1 = 9 characters, the ones between the @ and the dot. Copied down, the formula returns northwind, contoso, fabrikam, northwind and adventure-works.

In Microsoft 365 and Excel 2021 or later, LET makes this easier to read. It names each position once, then uses the names:

=LET(at, FIND("@", B2), dot, FIND(".", B2, at), MID(B2, at+1, dot-at-1))→ northwind

LEFT, RIGHT and MID return text, not numbers

LEFT, RIGHT and MID always return text, even when every character is a digit. That's why RIGHT kept the leading zero in 0187. It also means Excel doesn't treat the result as a number when it compares values:

=MID(D6, 4, 4)=2024→ FALSE
=VALUE(MID(D6, 4, 4))=2024→ TRUE

The text "2024" isn't equal to the number 2024. The same mismatch is a common cause of #N/A in lookups like XLOOKUP, and SUM skips text, so a column of extracted numbers adds up to 0. A quick tell: by default, text sits on the left of a cell and numbers on the right.

To convert the result, wrap it in VALUE, or put two minus signs in front. Both drop the leading zero:

=VALUE(RIGHT(D2, 4))→ 187
=--RIGHT(D2, 4)→ 187

The first minus turns the text into a negative number and the second turns it back. Arithmetic converts text too, so =MID(D6, 4, 4)+1 returns the number 2025.

Too many characters, zero and negative numbers

LEFT, RIGHT and MID are forgiving about lengths that are too long, but not about negative numbers. Here's what each returns for the 12 characters in D2:

FormulaResultWhy
=LEFT(D2)NLeaving out num_chars means 1
=LEFT(D2, 50)NW-2026-0187More characters than the text has returns all of it
=LEFT(D2, 0)Empty textZero characters
=RIGHT(D2, -1)#VALUE!num_chars can't be negative
=MID(D2, 10, 50)187MID stops at the end of the text
=MID(D2, 13, 4)Empty textPosition 13 is past the end, so there's nothing to return
=MID(D2, 0, 4)#VALUE!start_num must be 1 or more
=MID(D2, 4, -1)#VALUE!num_chars can't be negative

These errors usually come from a calculated argument, such as LEN(D2)-20 when the cell is shorter than you expected. The empty text results look blank, but they're text, so ISBLANK returns FALSE for the cell.

TEXTBEFORE and TEXTAFTER in Microsoft 365

In Microsoft 365, Excel 2024 and Excel for the web, TEXTBEFORE and TEXTAFTER do the FIND work for you. You give them the delimiter, and they return the text before or after it:

=TEXTBEFORE(B2, "@")→ emma.johnson
=TEXTAFTER(B2, "@")→ northwind.com
=TEXTBEFORE(TEXTAFTER(B2, "@"), ".")→ northwind

The third argument picks which delimiter to use, and a negative number counts from the end. This returns the text after the last dash:

=TEXTAFTER(D2, "-", -1)→ 0187

When the delimiter isn't there, they return #N/A rather than #VALUE!, and their optional if_not_found argument sets what to show instead. Use LEFT, RIGHT and MID with FIND when the file has to work in Excel 2021 or older, and for fixed positions like the store code, where LEFT is still the simplest formula.

Common LEFT, RIGHT and MID mistakes

ProblemWhat's going onFix
#VALUE! from a formula with FINDThe character isn't in the cell, or FIND's upper and lower case don't matchCheck the data, switch to SEARCH, or wrap the formula in IFERROR
#VALUE! from LEFT, RIGHT or MIDnum_chars is negative, or MID's start_num is below 1Check the calculated argument, such as LEN(D2)-20
The result ends with the @FIND's position includes the character itselfSubtract 1: FIND("@", B2)-1
The result starts with the @MID starts at the character itselfAdd 1: FIND("@", B2)+1
An empty result, or the wrong charactersExtra spaces count as charactersPut TRIM around the cell first
(55 instead of 555LEFT counts characters, and a bracket is a characterStart after the bracket with MID
A number that won't match, compare or add upLEFT, RIGHT and MID return textWrap the formula in VALUE, or put -- in front
Odd digits from a date or a priceThe functions use the stored number, not how it's formattedUse YEAR, MONTH or DAY for dates

Frequently asked questions about LEFT, RIGHT and MID

Do LEFT, RIGHT and MID work on numbers?

Yes, but the result is text: =LEFT(2026, 2) returns 20 as text. They work on the number Excel stores, not on how it's formatted. A price formatted as $1,250.00 is stored as 1250, so LEFT sees 1250. A date is stored as a serial number, so LEFT on a date returns digits of that number, not the year. Use YEAR, MONTH and DAY for dates, or TEXT to turn the date into text first.

How do I check whether a cell starts or ends with certain text?

Compare LEFT or RIGHT with the text. The comparison ignores upper and lower case:

=LEFT(D2, 2)="NW"→ TRUE
=RIGHT(B3, 4)=".org"→ TRUE

To count the cells that start with something, use a wildcard in COUNTIF, where * means any characters. The COUNTIF guide covers wildcards in detail:

=COUNTIF(D2:D6, "NW*")→ 2

To test whether the text appears anywhere in the cell, use =ISNUMBER(SEARCH("north", B2)), which returns TRUE. The FILTER guide uses the same test to list every matching row.

How do I split a cell into several columns?

For a one-off job, select the column and use Data > Text to Columns to split it at a delimiter such as a dash. Set the order number column to Text in the last step, or 0187 becomes 187. Flash Fill, with Ctrl+E, can also copy a pattern you type in the first row. In Microsoft 365, Excel 2024 and Excel for the web, TEXTSPLIT does it with a formula and spills the parts across the row:

=TEXTSPLIT(D2, "-")→ NW, 2026, 0187

For one part at a time, or in older versions, use LEFT, RIGHT and MID as shown above.

Which versions of Excel have LEFT, RIGHT and MID?

LEFT, RIGHT, MID, LEN, FIND and SEARCH work in every version of Excel, on Windows and Mac, and in Excel for the web. Google Sheets has them too, with the same arguments. Giving them a whole range, as in =RIGHT(C2:C6, 4), needs Microsoft 365 or Excel 2021 or later. TEXTBEFORE, TEXTAFTER and TEXTSPLIT need Microsoft 365, Excel 2024 or Excel for the web. For Microsoft's own examples, see the LEFT function, RIGHT function and MID function pages.

For more practice, the text topic has the free clean-name exercise on the same customer export, plus Premium exercises on the last four characters with RIGHT, the year with MID, the text before the @, the email domain and a first name from a messy cell.

Key takeaways

  • =LEFT(text, [num_chars]) and =RIGHT(text, [num_chars]) return characters from the start and the end, and =MID(text, start_num, num_chars) returns them from any position. Every character counts, spaces included.
  • When the length varies, find the cut point with FIND or SEARCH. =LEFT(B2, FIND("@", B2)-1) returns the text before a character, and RIGHT with LEN minus the position returns the text after it.
  • The results are always text, so leading zeros stay. Wrap them in VALUE, or put -- in front, when you need a number.
  • TRIM messy text first, use IFERROR when the character might be missing, and use TEXTBEFORE and TEXTAFTER in Microsoft 365 or Excel 2024.

Practice

Keep going with LEFT and RIGHT exercises

Reading gets you started. Solving a few of these is what makes it stick.

  1. 1Store codeFreeGet the 2-letter store code from the start of the order code in D3.→
  2. 2Text from the end PremiumGet the last 4 digits of the order code in D4.→
  3. 3Pull out the year PremiumExtract the year (the 4 digits in the middle) from the order code in D2.→
  4. 4Text before a character PremiumGet the part of the email in B6 that comes before the @.→
  5. 5Email domain PremiumGet the domain from the email in B4: everything after the @.→
  6. 6First name from a messy cell PremiumGet the first name from A5, cleaned up and capitalized: Noah.→
All text exercises →
Brian Millot

About the author

Brian Millot

Brian Millot is an indie hacker based in France who builds small, focused web products. He built and runs Excel Formula Practice, from the in-browser spreadsheet that checks your formulas to the exercise library, and he writes the formula guides.

Related guides

Practice makes formulas

Stop reading. Start typing formulas.

Every exercise is a real spreadsheet with instant feedback. The first two in every topic are free, no sign-up needed.