Extracting Text Using LEFT, RIGHT, and MID in Excel

Course: Advanced Excel

Topic: Extracting Text Using LEFT, RIGHT, and MID Functions

Course Link: Advanced Excel Course – JustAcademy

Demo Link: Register for a Free Course Demo

Website: JustAcademy Official Website

1. Introduction to Text Extraction in Excel

Text extraction is an important data-cleaning and data-manipulation technique in Microsoft Excel. It involves extracting specific characters from a text string based on their position or the number of characters required.

In real-world datasets, information is often stored in a single cell even when different parts of that information are needed separately. For example, a product code may contain a category, product number, and location identifier. Instead of manually separating these values, Excel provides text functions that automate the process.

The three essential functions used for extracting text are:

• LEFT: Extracts characters from the beginning of a text string.
• RIGHT: Extracts characters from the end of a text string.
• MID: Extracts characters from a specified position within a text string.

These functions are useful for cleaning customer records, separating codes, processing employee IDs, preparing reports, and organizing imported data.

2. Learning Objectives

After completing this topic, learners will be able to:

• Understand the syntax and purpose of LEFT, RIGHT, and MID.
• Extract characters from the beginning, end, or middle of a string.
• Work with employee IDs, product codes, email addresses, and customer data.
• Combine text extraction functions with other Excel formulas.
• Handle common errors and inconsistent text formats.
• Apply text extraction techniques to real-world reporting tasks.

3. Understanding the LEFT Function

3.1 What Is the LEFT Function?

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

It is particularly useful when the information you need appears at the start of a cell, such as a country code, department abbreviation, product prefix, or the first few digits of an identification code.

3.2 Syntax

=LEFT(text, [num_chars])

Arguments:

• text: The text string or cell reference from which characters are extracted. This argument is required.
• num_chars: The number of characters to extract from the left. This argument is optional and defaults to 1.

3.3 Example 1: Extracting Characters from a Product Code

Suppose cell A2 contains the product code ELEC4589.

Explanation: The first formula extracts four characters from the left side of the product code. The second extracts two characters, while the third extracts one character because the optional argument is omitted.

3.4 Example 2: Extracting a Department Code

Assume employee codes follow a pattern in which the first three characters represent the department.

Practical use: Extracting department codes makes it easier to group employees by department, create summaries, and prepare departmental reports.

3.5 Important Points About LEFT

• LEFT counts characters from the beginning of the text.
• If the number of characters is omitted, Excel returns the first character.
• If num_chars is greater than the text length, the entire text is returned.
• If num_chars is zero, the result is an empty text string.
• A negative num_chars value produces a #VALUE! error.

4. Understanding the RIGHT Function

4.1 What Is the RIGHT Function?

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

It is useful when information appears at the end of a value, such as the final digits of an account reference, a file extension, a location code, or the last characters of a product ID.

4.2 Syntax

=RIGHT(text, [num_chars])

Arguments:

• text: The text string or cell reference from which characters are extracted.
• num_chars: The number of characters to extract from the right. It defaults to 1 when omitted.

4.3 Example 1: Extracting the Numeric Portion of a Product Code

Suppose cell A2 contains ELEC4589.

Explanation: RIGHT(A2,4) returns the final four characters. Because the result is extracted as text, digits such as leading zeros can be preserved in the extracted string.

4.4 Example 2: Extracting a File Extension

Assume a worksheet contains file names in column A.

Practical use: Extracting extensions can help classify file names, organize file inventories, and identify different document formats.

4.5 Important Points About RIGHT

• RIGHT counts characters from the end of the text.
• If num_chars is omitted, Excel returns the final character.
• If num_chars exceeds the length of the text, the complete text is returned.
• If num_chars is zero, the result is an empty text string.
• A negative num_chars value produces a #VALUE! error.

5. Understanding the MID Function

5.1 What Is the MID Function?

The MID function extracts a specified number of characters from the middle of a text string. Unlike LEFT and RIGHT, MID allows you to define the starting position and the number of characters to return.

This function is helpful when the required information is located between other characters, such as a region code inside a product identifier or a year embedded in an employee reference.

5.2 Syntax

=MID(text, start_num, num_chars)

Arguments:

• text: The text string or cell reference to process.
• start_num: The position of the first character to extract. The first character in a text string is position 1.
• num_chars: The number of characters to return. This argument is required.

5.3 Example 1: Extracting a Year from an Employee Code

Suppose cell A2 contains the employee reference EMP2026458, where the four digits after EMP represent the year.

Explanation:

• The first three characters are E, M, and P.
• Position 4 is the first digit of the year.
• The formula extracts four characters starting from position 4.
• The result is 2026.

5.4 Example 2: Extracting a Region Code

Suppose product references use the format PR-NW-4589, where NW identifies the region.

Practical use: Region extraction can support geographical analysis, regional sales reporting, and inventory classification.

5.5 Important Points About MID

• Character positions begin at 1, not 0.
• The start_num argument must be at least 1.
• If num_chars is zero, MID returns an empty text string.
• If start_num exceeds the text length, MID returns an empty text string.
• If the requested number of characters extends beyond the end of the text, MID returns the remaining characters.
• Negative start_num or num_chars values produce a #VALUE! error.

6. Comparison of LEFT, RIGHT, and MID

7. Working with Text Extraction in Real-World Data

7.1 Extracting Parts of Customer IDs

Consider customer IDs that follow the format CUS-MH-1058.

This technique can separate identifiers into meaningful fields for analysis. It works reliably when the input follows a consistent format.

7.2 Extracting User Names from Email Addresses

For an email address such as [email protected], the LEFT function can extract the text before the @ symbol when the position of @ is found with FIND.

=LEFT(A2,FIND("@",A2)-1)

Result: rahul.sharma

Explanation: FIND("@",A2) returns the position of the @ symbol. Subtracting 1 ensures that the @ symbol is excluded from the result.

To extract the domain portion after @, use MID:

=MID(A2,FIND("@",A2)+1,LEN(A2))

Result: example.com

This formula begins one character after @ and returns the remaining text. For cleaner dynamic formulas, you can also use LEN(A2)-FIND("@",A2) as the num_chars argument.

7.3 Extracting a Fixed-Length Code from a Reference

Suppose cell A2 contains ORD2026MUM458, and the year always occupies characters 4 through 7.

=MID(A2,4,4)

Result: 2026

This method is suitable for fixed-format order references. If the position or length varies between records, use delimiter-based formulas or more advanced text functions instead.

8. Combining LEFT, RIGHT, and MID with Other Functions

8.1 Combining LEFT with FIND

FIND identifies the position of a character or text sequence. Combining it with LEFT allows you to extract all characters before a separator.

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

If A2 contains SALES-MUMBAI, the result is SALES.

8.2 Combining RIGHT with LEN

LEN returns the total number of characters in a text string. You can use it to calculate the number of characters to extract from the end.

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

If A2 contains SALES-MUMBAI, the result is MUMBAI.

This formula assumes the separator occurs once in the expected location. If the text contains several hyphens, the formula may need adjustment.

8.3 Combining MID with FIND

MID can extract text between two known separators when the starting position and length are calculated dynamically.

Suppose A2 contains EMP-MH-2026. To extract MH, use:

=MID(A2,FIND("-",A2)+1,FIND("-",A2,FIND("-",A2)+1)-FIND("-",A2)-1)

Result: MH

The formula finds the first hyphen, begins after it, and calculates the number of characters before the second hyphen. It is useful for extracting a middle component when the component length varies.

8.4 Combining with IFERROR

When expected delimiters are missing, FIND may return an error. IFERROR can provide a fallback value instead.

=IFERROR(LEFT(A2,FIND("-",A2)-1),A2)

If a hyphen is found, the formula returns the text before it. If no hyphen is found, it returns the original cell value.

9. Practical Business Applications

9.1 Sales and Inventory Management

• Extract product categories from SKU prefixes.
• Separate warehouse or region identifiers from product references.
• Identify the numeric portion of inventory codes.
• Standardize product references before using lookup formulas.

9.2 Human Resources

• Extract department codes from employee IDs.
• Separate fixed-format joining years from staff references.
• Prepare employee records for filtering and reporting.
• Organize payroll references according to a documented code format.

9.3 Marketing and Customer Data

• Extract user names and domains from email addresses.
• Separate campaign prefixes from tracking codes.
• Prepare imported contact lists for segmentation.
• Standardize customer IDs for matching across worksheets.

9.4 Reporting and Data Cleaning

• Remove unwanted prefixes or suffixes by extracting the required portion.
• Prepare text fields for PivotTables and charts.
• Separate structured identifiers into multiple columns.
• Build reusable formulas for recurring monthly reports.

10. Common Errors and Troubleshooting

11. Best Practices for Text Extraction

• Understand the source format: Identify whether the required portion is at the beginning, end, or middle.
• Count positions accurately: Remember that spaces, hyphens, and other punctuation count as characters.
• Use cell references: References such as A2 make formulas easier to copy and maintain.
• Test different records: Validate formulas against short, long, and irregular examples.
• Handle missing separators: Use IFERROR where an absent delimiter is a realistic possibility.
• Preserve identifiers: Treat codes as text when leading zeros or exact formatting must be maintained.
• Use dynamic methods where needed: Combine MID, LEFT, and RIGHT with FIND or SEARCH if text positions depend on separators.
• Document assumptions: Make clear when a formula expects a fixed number of characters or a consistent code structure.

12. Hands-On Practice Exercise

Enter the following values into column A of an Excel worksheet, starting from cell A2. Use the appropriate functions to extract the requested information.

Practice Formulas and Expected Results

13. Mini Project: Employee Code Analysis

Project objective: Create an Excel worksheet that separates employee references into department codes, joining years, and employee numbers.

Sample Data

Suggested Formulas

Assuming the employee code is in A2 and follows the fixed format of three department letters, four year digits, and three employee-number digits:

Department:
=LEFT(A2,3)

Joining Year:
=MID(A2,4,4)

Employee Number:
=RIGHT(A2,3)

Project Tasks

1. Enter at least 20 employee codes in a worksheet.
2. Use LEFT to extract the department code.
3. Use MID to extract the joining year.
4. Use RIGHT to extract the employee number.
5. Copy the formulas down for all records.
6. Check the extracted values against the original codes.
7. Create a summary of employees by department using a PivotTable or COUNTIF.

Expected outcome: A structured employee dataset that is easier to filter, analyze, and summarize. These formulas assume all codes follow the same fixed-length pattern.

14. Interview Questions and Answers

Q1. What is the LEFT function in Excel?

Answer: LEFT extracts a specified number of characters from the beginning of a text string. For example, =LEFT("EXCEL2026",5) returns EXCEL.

Q2. What is the RIGHT function used for?

Answer: RIGHT extracts characters from the end of a string. For example, =RIGHT("EXCEL2026",4) returns 2026.

Q3. How does MID differ from LEFT and RIGHT?

Answer: MID extracts characters from a specified starting position, whereas LEFT and RIGHT extract characters from the beginning and end respectively.

Q4. What happens when num_chars is omitted in LEFT?

Answer: Excel returns one character from the beginning of the text.

Q5. Can these functions extract numbers from text?

Answer: Yes. They return extracted characters as text. If a numeric calculation is required, the result may need conversion using VALUE or another suitable method.

Q6. How can you extract text before a hyphen?

Answer: Use =LEFT(A2,FIND("-",A2)-1), assuming the hyphen exists in the source text.

Q7. How do you extract a specific middle section?

Answer: Use MID with the text, starting position, and number of characters, such as =MID(A2,4,3).

Q8. What error can occur if num_chars is negative?

Answer: Excel returns a #VALUE! error.

Q9. Are LEFT, RIGHT, and MID case-sensitive?

Answer: These functions extract characters without comparing text case, so they preserve the case of the extracted characters but do not perform case-sensitive matching.

Q10. When should you combine text extraction functions with FIND?

Answer: Combine them when the required text is separated by a delimiter and its position may change between records.

15. Quick Revision Notes

• LEFT starts counting at the beginning of the text.
• RIGHT starts counting at the end of the text.
• MID requires a starting position and character count.
• All three functions return text strings.
• Use FIND or SEARCH to locate separators when text positions vary.
• Validate formulas whenever source formats are inconsistent.

16. Conclusion

The LEFT, RIGHT, and MID functions are fundamental tools for text manipulation in Advanced Excel. LEFT extracts characters from the beginning of a string, RIGHT extracts characters from the end, and MID retrieves characters from a specified position.

When combined with functions such as FIND, SEARCH, LEN, TRIM, and IFERROR, these formulas can handle a wide range of data-cleaning and reporting tasks. Practicing with employee codes, customer IDs, product references, and email addresses will help develop the skills needed to work efficiently with real-world Excel datasets.

For more learning opportunities, explore the Advanced Excel Course at JustAcademy, visit the JustAcademy website, or register for a course demo.