How to Use LEFT, RIGHT, and MID Functions in Excel

Microsoft Excel is not just a tool for numbers and calculations; it is also a powerful platform for working with text data. In many real-world scenarios, data is not perfectly structured. You may receive full names instead of separate first and last names, product codes that combine letters and numbers, or dates stored as text. This is where Excel’s text functions become essential.

Among the most commonly used text functions are LEFT, RIGHT, and MID. These functions allow you to extract specific parts of a text string based on position. Whether you are cleaning imported data, preparing reports, or automating workflows, understanding how to use these functions effectively can save you significant time and effort.

In this article, we will explore the LEFT, RIGHT, and MID functions in Excel in detail. We will cover their syntax, practical examples, common use cases, and how they can be combined with other Excel functions for more advanced text manipulation.

Understanding Text Functions in Excel

Before diving into each function individually, it is important to understand how Excel treats text. Any sequence of characters—letters, numbers, spaces, or symbols—is considered text when stored in a cell. Text functions operate on these characters based on their position within the string.

For example, in the text:

INV-2025-045

• Each letter, number, and hyphen counts as one character.
• Excel numbers characters starting from 1, not 0.

LEFT, RIGHT, and MID are position-based text functions, meaning they extract characters from specific locations within a text string.

The LEFT Function in Excel

What the LEFT Function Does

The LEFT function extracts a specified number of characters from the beginning (left side) of a text string.

It is especially useful when important information always appears at the start of a value, such as country codes, prefixes, or department identifiers.

Syntax of the LEFT Function

LEFT(text, [num_chars])

Parameters:

• text – The text string from which you want to extract characters.
• num_chars (optional) – The number of characters to extract from the left. If omitted, Excel extracts 1 character by default.

Basic LEFT Function Example

Suppose cell A1 contains:

ExcelGuide

Formula:

=LEFT(A1, 5)

Result:

Excel

Excel extracts the first five characters from the left side of the text.

Practical Uses of the LEFT Function

Extracting Area Codes from Phone Numbers

If cell A2 contains:

(212) 555-7890

To extract the area code:

=LEFT(A2, 5)

Result:

(212)

Extracting Year from a Text-Based Date

If cell A3 contains:

2025-01-14

To extract the year:

=LEFT(A3, 4)

Result:

2025

Using LEFT with Other Functions

LEFT becomes even more powerful when combined with functions like LEN, FIND, or SEARCH. For example, extracting text before a delimiter such as a hyphen or space.

The RIGHT Function in Excel

What the RIGHT Function Does

The RIGHT function extracts a specified number of characters from the end (right side) of a text string.

This function is commonly used to extract suffixes, file extensions, last digits of IDs, or month/day values from dates stored as text.

Syntax of the RIGHT Function

RIGHT(text, [num_chars])

Parameters:

• text – The text string you want to extract characters from.
• num_chars (optional) – The number of characters to extract from the right. If omitted, Excel extracts 1 character.

Basic RIGHT Function Example

If cell A4 contains:

Spreadsheet

Formula:

=RIGHT(A4, 5)

Result:

sheet

Practical Uses of the RIGHT Function

Extracting File Extensions

If cell A5 contains:

report.xlsx

Formula:

=RIGHT(A5, 4)

Result:

xlsx

Extracting Last Four Digits of an ID

If cell A6 contains:

EMP-2025-7843

Formula:

=RIGHT(A6, 4)

Result:

7843

RIGHT Function with Dynamic Lengths

Sometimes, you may not know the exact number of characters to extract. In such cases, combining RIGHT with LEN and FIND allows for flexible extraction.

The MID Function in Excel

What the MID Function Does

The MID function extracts characters from the middle of a text string, starting at a specified position and continuing for a specified number of characters.

This function is particularly useful when the information you need is neither at the beginning nor at the end of the text.

Syntax of the MID Function

MID(text, start_num, num_chars)

Parameters:

• text – The text string to extract characters from.
• start_num – The position of the first character to extract (starting from 1).
• num_chars – The number of characters to extract.

Basic MID Function Example

If cell A7 contains:

INV-2025-045

Formula:

=MID(A7, 5, 4)

Result:

2025

This extracts four characters starting from the fifth position.

Practical Uses of the MID Function

Extracting Department Codes

If cell A8 contains:

HR-EMP-0098

To extract “EMP”:

=MID(A8, 4, 3)

Result:

EMP

Extracting Middle Digits from Codes

If cell A9 contains:

AB123CD

Formula:

=MID(A9, 3, 3)

Result:

123

Comparing LEFT, RIGHT, and MID

Each function has a specific role, and choosing the right one depends on where the desired text appears.

Combining LEFT, RIGHT, and MID with Other Functions

Using FIND or SEARCH

The FIND and SEARCH functions locate the position of a character or word within a text string. When combined with LEFT, RIGHT, or MID, they allow you to extract text dynamically.

Example: Extract Text Before a Hyphen

If cell A10 contains:

Sales-2025

Formula:

=LEFT(A10, FIND("-", A10) - 1)

Result:

Sales

Using LEN for Dynamic Extraction

The LEN function counts the number of characters in a text string.

Example: Extract Text After a Space

If cell A11 contains:

John Smith

Formula:

=RIGHT(A11, LEN(A11) - FIND(" ", A11))

Result:

Smith

Common Errors and How to Avoid Them

#VALUE! Error

This error often occurs when:

• The start_num in MID is greater than the text length.
• FIND cannot locate the specified character.

Tip: Use error-handling functions like IFERROR to prevent formulas from breaking.

Extracting Incorrect Characters

This usually happens when spaces or hidden characters are overlooked. Always verify the exact structure of your text, including spaces and symbols.

Real-World Use Cases

• Data Cleaning: Splitting imported data into usable columns.
• Reporting: Extracting codes or dates for analysis.
• Automation: Preparing structured data for formulas, pivot tables, or dashboards.
• Text Standardization: Normalizing inconsistent text entries.

Best Practices When Using LEFT, RIGHT, and MID

1. Always inspect your data to understand its structure.
2. Combine text functions with logical functions for flexibility.
3. Use helper columns for complex transformations.
4. Document formulas when sharing workbooks with others.
5. Test formulas on sample data before applying them to large datasets.

Conclusion

The LEFT, RIGHT, and MID functions are foundational tools for text manipulation in Excel. They allow you to extract exactly the characters you need from text strings, making them indispensable for data preparation and analysis.

By mastering these functions and learning how to combine them with others like LEN, FIND, and SEARCH, you can handle even complex text extraction tasks with confidence. Whether you are a beginner learning Excel basics or an advanced user cleaning large datasets, these functions will significantly enhance your productivity.

Understanding how to use LEFT, RIGHT, and MID effectively is a key step toward becoming more efficient and accurate in Excel.