How to Use LEFT, RIGHT and MID in Excel: Extract Text With Formulas

shape
shape
shape
shape
shape
shape
shape
shape
How to Use LEFT, RIGHT and MID in Excel: Extract Text With Formulas - featured image

The LEFT, RIGHT and MID functions take a piece of text out of a cell. LEFT takes characters from the start, RIGHT takes them from the end, and MID takes them from the middle. They are the basic tools for cleaning data: splitting a product code, getting a country code from a phone number, finding a year inside an ID, or taking the first name from a full name. This guide explains the syntax of each function, shows worked examples that you can check by counting characters, and then combines them with FIND, SEARCH and LEN to handle text of different lengths. It ends with the common errors and a few practice problems.

Updated November 2026

In this guide

  1. The three functions at a glance
  2. Example data
  3. LEFT: take characters from the start
  4. RIGHT: take characters from the end
  5. MID: take characters from the middle
  6. Convert the result to a number
  7. Use FIND to locate a character
  8. Use LEN for the last part
  9. Extract text between two characters
  10. Remove extra spaces first
  11. Handle errors with IFERROR
  12. Newer functions: TEXTBEFORE and TEXTAFTER
  13. A practical example: clean a list of codes
  14. Common mistakes
  15. Practice problems
  16. Quick recap

The three functions at a glance

LEFT(text, num_chars) returns the first characters of a text. RIGHT(text, num_chars) returns the last characters. MID(text, start_num, num_chars) returns characters from the middle, starting at a position and taking a number of characters. In all three, spaces count as characters, and the result is text, even if it looks like a number. If you leave out num_chars in LEFT or RIGHT, Excel returns one character. In MID, all three arguments are required.

Example data

Imagine product codes such as ABC-2026-0045 in column A. The code has three parts: three letters, a four-digit year and a four-digit number, with a hyphen between each. Count the characters: ABC is 3, the hyphen is the 4th, 2026 are the 5th to 8th, the next hyphen is the 9th, and 0045 are the 10th to 13th. So the code has 13 characters. Counting positions like this is the key to using these functions correctly.

LEFT: take characters from the start

To get the three letters, use =LEFT(A2,3). The result is ABC. LEFT starts at the first character and counts three. To get the first name from John Smith, you need to know how many characters it has, so you combine it with FIND, as shown below. LEFT is also used to get an area code from a phone number, a prefix from an invoice number and the first letter of a name for initials, with =LEFT(A2,1). If num_chars is longer than the text, LEFT returns the whole text.

RIGHT: take characters from the end

To get the last four digits, 0045, use =RIGHT(A2,4). RIGHT counts from the end of the text, which makes it perfect for codes where the end part has a fixed length, such as the last four digits of a card or an order number. To get the file extension from report.xlsx, you need the length of the extension, so you use FIND with LEN as shown in the next sections. If you want to remove characters from the end instead, use LEFT with LEN, such as =LEFT(A2,LEN(A2)-4).

MID: take characters from the middle

To get the year 2026 from ABC-2026-0045, use =MID(A2,5,4). The 5 is the position where 2026 starts, and the 4 is the number of characters to take. MID is the function to use when the part you want is not at the start or the end. If the start number is larger than the length of the text, MID returns an empty text. If the number of characters is too large, MID returns everything to the end of the text.

Convert the result to a number

LEFT, RIGHT and MID always return text. So =MID(A2,5,4) returns the text 2026, not the number 2026. A text year will not add or sort like a number. To convert it, wrap it in VALUE, as in =VALUE(MID(A2,5,4)), or add zero, as in =MID(A2,5,4)+0. You can also multiply by 1. Our text to number guide lists more methods. A converted year of 2026 can then be used in a calculation, such as 2027 minus the year to get 1.

Use FIND to locate a character

The real power appears when you combine the functions with FIND, which returns the position of a character. For the full name John Smith in A2, =FIND(" ",A2) returns 5, because the space is the fifth character. The first name is then =LEFT(A2,FIND(" ",A2)-1), which takes 4 characters, John. The last name is =MID(A2,FIND(" ",A2)+1,100), which starts at character 6 and takes up to 100 characters, so it returns Smith. Using 100 is a simple way to say all the rest.

Use LEN for the last part

LEN returns the number of characters in a text. For John Smith it returns 10. The last name can also be found with RIGHT: =RIGHT(A2,LEN(A2)-FIND(" ",A2)). That is 10 minus 5, which is 5, so RIGHT returns the last 5 characters, Smith. Both methods give the same result. Choose the one you find easier to read. Our split text guide shows other ways to separate text, including Text to Columns and TEXTSPLIT.

Extract text between two characters

For an email address such as ali@softotech.net, you may want the domain. Use =MID(A2,FIND("@",A2)+1,100), which returns softotech.net. To get the name before the at sign, use =LEFT(A2,FIND("@",A2)-1), which returns ali. For text between two hyphens, such as the year in our code, you can use two FIND functions: the first hyphen is at position 4 and the second at 9, so the year starts at 5 and has 9 minus 4 minus 1, which is 4 characters.

Remove extra spaces first

Data from other systems often has extra spaces, which breaks the counting. A name with a space at the start, such as a space and then John, shifts every position by one. Use TRIM to clean the text before you extract, for example =LEFT(TRIM(A2),FIND(" ",TRIM(A2))-1). Our remove spaces guide explains TRIM and the other methods. Cleaning first is a good habit, since it prevents hidden errors that are hard to find.

Handle errors with IFERROR

If the character you search for is missing, FIND returns a #VALUE! error. For a name with only one word, such as Madonna, the first name formula fails. Wrap it with IFERROR: =IFERROR(LEFT(A2,FIND(" ",A2)-1),A2). If the space is not found, the formula returns the whole text. This way, the column does not fill with errors. FIND is case sensitive, and SEARCH is not, so use SEARCH if you do not know the case of the letters. SEARCH also allows wildcards.

Newer functions: TEXTBEFORE and TEXTAFTER

Excel 365 has TEXTBEFORE and TEXTAFTER, which are easier for many tasks. =TEXTBEFORE(A2," ") returns the first name, and =TEXTAFTER(A2," ") returns the rest. For our email example, =TEXTAFTER(A2,"@") returns the domain. These functions are not in older versions, so if you share the file with people who use Excel 2016 or 2019, the formulas will show an error. LEFT, RIGHT and MID work in every version, which is why they are still worth learning.

A practical example: clean a list of codes

Suppose column A holds 500 product codes in the format ABC-2026-0045. You want three new columns: the prefix, the year and the number. In B2 use =LEFT(A2,3), in C2 use =VALUE(MID(A2,5,4)) and in D2 use =VALUE(RIGHT(A2,4)). Fill the formulas down. For the first code the results are ABC, 2026 and 45, because VALUE removes the leading zeros. If you need to keep the zeros, skip VALUE and leave the number as text. After that, you can sort by year, count codes per prefix with COUNTIF, or build a PivotTable from the new columns.

Common mistakes

The most common mistake is to miscount the positions, so the result is off by one. Count slowly, or use FIND to get the position. Another is to forget that the results are text. A third is to forget that a space counts as a character. A fourth is to use a formula on a number such as a date, which Excel treats as a serial number, so LEFT of a date returns the first digits of the serial number and not the day. A fifth is to forget IFERROR when the data is inconsistent. A sixth is to copy the formula without anchoring the reference.

Practice problems

Try these. Problem 1: =LEFT("Excel",2) returns Ex. Problem 2: =RIGHT("Excel",3) returns cel. Problem 3: =MID("Excel",2,3) returns xce. Problem 4: for ABC-2026-0045, what returns 0045? The answer is RIGHT with 4. Problem 5: for the full name Sara Khan, write a formula for the last name, which is Khan, using FIND and MID.

Quick recap

LEFT takes characters from the start, RIGHT from the end and MID from a chosen position. Count the characters and spaces carefully, or locate the position with FIND or SEARCH. Use LEN to work out how many characters remain, convert the result to a number with VALUE when needed, clean the data with TRIM and protect the formula with IFERROR. In Excel 365, TEXTBEFORE and TEXTAFTER make many of these tasks shorter, but the three classic functions work everywhere.

Frequently asked questions

What does LEFT do in Excel?

It returns a number of characters from the start of a text.

What is the syntax of MID?

MID(text, start_num, num_chars).

Why does LEFT return text instead of a number?

Text functions always return text, so use VALUE to convert the result.

How do I get the first name from a full name?

Use LEFT with FIND to find the first space.

What is the difference between FIND and SEARCH?

FIND is case sensitive, and SEARCH is not.

Related guides and free tools

Leave a Reply

Your email address will not be published. Required fields are marked *