Excel provides a set of functions that allow you to extract specific parts of a text string, such as the LEFT, RIGHT, and MID functions. These functions are incredibly useful when you need to manipulate or analyze text data. Whether you need to extract a few characters from the beginning, end, or middle of a string, these functions have you covered.
Syntax
| Function | Syntax | Description |
|---|---|---|
| LEFT | LEFT(text, [num_chars]) | Extracts a specified number of characters from the start (left) of a string. |
| RIGHT | RIGHT(text, [num_chars]) | Extracts a specified number of characters from the end (right) of a string. |
| MID | MID(text, start_num, num_chars) | Extracts a specified number of characters from the middle of a string. |
Example
Consider the following data set where you have a list of product codes, and you need to extract different parts of the code for analysis.
| Product Code | LEFT (First 3 characters) | MID (Characters 5 to 8) | RIGHT (Last 4 characters) |
|---|---|---|---|
| PRD1234ABCD | =LEFT(A2, 3) | =MID(A2, 5, 4) | =RIGHT(A2, 4) |
| XYZ9876LMNOP | =LEFT(A3, 3) | =MID(A3, 5, 4) | =RIGHT(A3, 4) |
| ABX4567EFGH | =LEFT(A4, 3) | =MID(A4, 5, 4) | =RIGHT(A4, 4) |
This will give us this result:
| Product Code | LEFT (First 3 characters) | MID (Characters 5 to 8) | RIGHT (Last 4 characters) |
|---|---|---|---|
| PRD1234ABCD | PRD | 234A | ABCD |
| XYZ9876LMNOP | XYZ | 9876 | MNOP |
| ABX4567EFGH | ABX | 4567 | EFGH |
In this example:
- LEFT Function is used to extract the first three characters.
- MID Function is used to extract four characters starting from the fifth character.
- RIGHT Function is used to extract the last four characters.
Practice Exercise 1
Task: Using the data below, extract the first 2 characters using the LEFT function, the middle 4 characters starting from the third position using the MID function, and the last 3 characters using the RIGHT function.
Practice Exercise 2
Task: Given the following codes, extract the first 4 characters using the LEFT function, the next 5 characters starting from the fifth position using the MID function, and the last 2 characters using the RIGHT function.
Solution for Exercise 1
- LEFT Function: Use
=LEFT(A2, 2)to extract the first two characters. - MID Function: Use
=MID(A2, 3, 4)to extract the middle four characters starting from the third position. - RIGHT Function: Use
=RIGHT(A2, 3)to extract the last three characters.
Solution for Exercise 2
- LEFT Function: Use
=LEFT(A2, 4)to extract the first four characters. - MID Function: Use
=MID(A2, 5, 5)to extract the next five characters starting from the fifth position. - RIGHT Function: Use
=RIGHT(A2, 2)to extract the last two characters.
These examples and exercises should help you get comfortable using the LEFT, RIGHT, and MID functions in Excel for extracting text data.