LEFT, RIGHT and MID Functions in Excel: Extract Text Like a Pro

Raw data rarely arrives in the exact format you need. Product codes, email addresses, phone numbers, and file names all contain segments that you need to split apart. The LEFT, RIGHT, and MID functions are Excel's core text-extraction tools. LEFT pulls characters from the beginning of a string, RIGHT pulls from the end, and MID pulls from any position in the middle. Together, they handle the vast majority of text-splitting tasks — without any add-ins or formulas longer than one line.

What Do LEFT, RIGHT, and MID Do?

Each function targets a different part of a text string. LEFT starts from character 1. RIGHT starts from the last character and works backward. MID, by contrast, starts from any character position you specify. The visual below shows how all three work on the same string.

Syntax for All Three Functions

Example 1: Extract a Fixed-Length Code Segment

Fixed-length codes are the simplest case. When every code follows the same structure — for example, a 3-character country prefix, a hyphen, and a 4-digit year — you can extract each part with a hardcoded character count. This works well for product codes, order IDs, and account numbers.

Example 2: Extract Text Before or After a Delimiter

Variable-length strings need a smarter approach. Instead of a fixed character count, you find the delimiter position first using FIND or SEARCH. Then you use that position to calculate exactly how many characters to extract with LEFT or RIGHT.

How to Use FIND with LEFT and RIGHT

FIND returns the character position of a delimiter — such as "@" in an email address, or "/" in a file path. Consequently, you can subtract 1 from that position to get everything before the delimiter, and use LEN minus that position to get everything after it.

Example 3: Extract a File Extension with RIGHT and FIND

File extensions vary in length — ".csv" has 4 characters, while ".xlsx" has 5 and ".txt" has 4. Therefore, a fixed character count will not work reliably. Instead, find the last dot and use RIGHT to extract everything after it.

Example 4: Extract the First Name and Last Name

Full names stored as "First Last" in a single cell are one of the most common data-cleaning challenges. LEFT with FIND handles the first name. RIGHT with LEN and FIND handles the last name. Additionally, TRIM cleans up any stray spaces that might cause blank results.

Example 5: Extract the Middle Segment Between Two Delimiters

MID is at its most powerful when you need something from the middle of a string — between two known delimiters. The formula finds the first delimiter, adds 1 to skip it, then finds the second delimiter to calculate the length. Consequently, it handles variable-length middle segments reliably.

Example 6: Dynamic Extraction with LEN

LEN counts the total characters in a string. Combined with LEFT, RIGHT, and MID, it enables dynamic formulas that adapt to any string length. For example, you can remove a known prefix or suffix, or extract everything except the last N characters.

How to Fix Common Errors

#VALUE! from FIND or SEARCH

FIND returns #VALUE! when the delimiter does not exist in the string. For instance, if one row contains no "@" symbol, the email formula will error. Wrap the entire formula in IFERROR to handle those cases cleanly: =IFERROR(LEFT(A2, FIND("@",A2)-1), A2).

Result is blank or shows spaces

The source data contains leading or trailing spaces that FIND picks up as part of the string. As a result, the extracted text shifts by one or more characters. Always clean source data with TRIM before extracting. Wrap the text argument in TRIM: =LEFT(TRIM(A2), FIND(" ", TRIM(A2))-1).

MID returns more characters than expected

If num_chars in MID is larger than the remaining string, MID simply returns everything to the end without an error. This is intentional behaviour. However, if you expected a fixed length and got more characters, recheck your start position — it may be off by one.

Frequently Asked Questions

• What is the difference between LEFT, RIGHT, and MID in Excel?+
  LEFT extracts a set number of characters from the beginning of a text string. RIGHT extracts from the end. MID extracts from any position in the middle — you specify the start position and the number of characters. Together, the three functions cover every part of a string. All three return text, even if the extracted characters look like numbers.
• How do I extract text before a specific character?+
  Use LEFT combined with FIND. FIND returns the position of the delimiter, and LEFT takes everything before it: =LEFT(A2, FIND("-",A2)-1). The minus 1 excludes the delimiter itself. If the delimiter might be missing from some rows, wrap the formula in IFERROR to prevent #VALUE! errors.
• How do I extract text after a specific character?+
  Use RIGHT combined with LEN and FIND: =RIGHT(A2, LEN(A2) - FIND("-",A2)). This calculates the total string length, subtracts the position of the delimiter, and extracts the remaining characters. The result excludes the delimiter itself. Change "-" to any other character to suit your data.

More Questions About Text Extraction

• What is the difference between FIND and SEARCH?+
  FIND is case-sensitive — it distinguishes between uppercase and lowercase letters. SEARCH is case-insensitive. For delimiter-based extraction, it makes no practical difference. However, when you search for a specific word or letter where case matters, use FIND. Additionally, SEARCH supports wildcard characters (? and *) while FIND does not.
• Do LEFT, RIGHT, and MID return numbers or text?+
  All three functions always return text, even when the extracted characters are digits. Consequently, you cannot use the result directly in arithmetic without first converting it. To convert text to a number, wrap the formula in VALUE: =VALUE(RIGHT(A2,4)). Alternatively, multiply by 1 or add 0 to trigger an automatic conversion.
• Which Excel versions support LEFT, RIGHT, and MID?+
  All three functions have been available since Excel 2000 and work in every version up to Microsoft 365. They also work in Excel for the web and Google Sheets. No special version, add-in, or setting is required. This makes them the most universally compatible text-extraction tools in Excel.