Excel String Functions: LEFT, RIGHT, MID, LEN and FIND
In this tutorial, you will learn how to use Excel string functions to retrieve specific text from a string.
| string_function | description |
|---|---|
| LEFT | Get characters from the left side of a string |
| RIGHT | Get characters from the right side of a string |
| MID | Get characters from the middle of a string |
| LEFT with FIND | Get all characters before a symbol |
| RIGHT with LEN, FIND | Get all characters after a symbol |
| MID with FIND | Get all characters between two symbols |
Extract Same Length String
LEFT
Suppose, you have the following table in Excel:
| A | B |
|---|---|
| ID | ID_cleaned |
| 12345-ID | |
| 23456-ID | |
| 34567-ID |
You want to extract the digits from the ID column.
That is, you could use the LEFT string function:
=LEFT(cell,number_of_characters_to_be_extracted_from_the_left)
More specifically, you need to extract the first five characters.
Type the following formula in cell B2:
=LEFT(A2,5)
Copy-drag the formula over to B4 and you should get:
| A | B |
|---|---|
| ID | ID_cleaned |
| 12345-ID | 12345 |
| 23456-ID | 23456 |
| 34567-ID | 34567 |
RIGHT
Suppose now, you have the following table in Excel:
| A | B |
|---|---|
| ID | ID_cleaned |
| ID-12345 | |
| ID-23456 | |
| ID-34567 |
You want to extract the digits from the ID column.
That is, you could use the RIGHT string function:
=RIGHT(cell,number_of_characters_to_be_extracted_from_the_right)
More specifically, you need to extract the first five characters from the right.
Type the following formula in cell B2:
=RIGHT(A2,5)
Copy-drag the formula over to B4 and you are done.
MID
Suppose now, you have the following table in Excel:
| A | B |
|---|---|
| ID | ID_cleaned |
| ID-12345-CUST | |
| ID-23456-CUST | |
| ID-34567-CUST |
You want to extract the digits from the ID column.
That is, you could use the MID string function:
=MID(cell,starting_positon_number,end_position_number)
More specifically, you need to extract the characters starting at the fourth position and ending at the eigth position.
Type the following formula in cell B2:
=MID(A2,4,8)
Copy-drag the formula over to B4 and you are done.
Extract String with Varying Length
LEFT with FIND
Suppose now, you have the following table in Excel:
| A | B |
|---|---|
| ID | ID_cleaned |
| 123-ID | |
| 2345-ID | |
| 34567-ID |
Note that, now, the ids have varying lengths.
Thus, you need use FIND together with LEFT to extract the digits.
That is, find the hyphen and give me all characters that come before it:
=LEFT(A2,FIND("-",A2)-1)
where -1 at the end is needed to leave out the hyphen in the result:
| A | B |
|---|---|
| ID | ID_cleaned |
| 123-ID | 123 |
| 2345-ID | 2345 |
| 34567-ID | 34567 |
RIGHT with LEN and FIND
Suppose now, you have the following table in Excel:
| A | B |
|---|---|
| ID | ID_cleaned |
| ID-123 | |
| ID-2345 | |
| ID-34567 |
Now, you need to calculate the length of the digits for each row. That is, substract the position number of the hyphen from the length of the cell:
=RIGHT(A2,LEN(A2)-FIND("-",A2))
MID with FIND
Suppose now, you have the following table in Excel:
| A | B |
|---|---|
| ID | ID_cleaned |
| ID-123-CUST | |
| ID-2345-CUST | |
| ID-34567-CUST |
The solution:
=MID(A2,FIND("-",A2)+1,FIND("-",A2,FIND("-",A2)+1)-FIND("-",A2)-1)
That's it! You just learned how to apply to extract strings in Excel.