How to Extract Substrings in Excel with LEFT, RIGHT, MID Functions
Have you ever needed to get the first and last name from a full name? Or maybe pull the street name from an address? 🤔
Tasks like these are easy!
But when you have hundreds or even thousands of lines of data, well…
…things become absolutely tedious! 😱
Lucky for us, Microsoft Excel provides the LEFT, RIGHT, and MID functions.
In this Excel tutorial, you learn to turn this…
You also learn how to unlock their full potential using LEN and FIND!
If you want to tag along, please download my sample data workbook here.
Table of Contents
Let’s dive right in!
Open practice workbook.
You’ll find the Contact Names and Contact Numbers of several people.
This could be a list of potential customers of a business. Or, a list of teachers’ contacts for students.
Information like this is usually split up into smaller bits for use in typical applications.
For example, you might need the Area Code for each contact.
To get this, you use the LEFT function.
Extract text string using the LEFT function
The LEFT function extracts a given number of characters starting from the left side of a string.
1. Type “=LEFT(” in cell C2 where you want the first Area Code.
2. The first argument of the LEFT function is text, this is the source text string.
Typically, this is the cell reference and in this case, it’s B2.
And put a comma to separate the next argument.
3. Next input is num_chars. This is the number of characters from the left of the string that will be extracted.
If you count the characters of the Area Code including the parentheses, you get 5.
So, input 5 and close the formula with a right parenthesis like this:
4. Hit Enter.
5. Let’s fill up the rest of the rows by double-clicking or dragging down the fill handle.
Now, you need the Extension number and you can get this using the RIGHT function.
Extract text string using the RIGHT function
The RIGHT function extracts a given number of characters starting from the right side of a string.
It has the exact same arguments, text and num_chars, as the LEFT function.
1. You want the first Extension number to appear in cell D2, so, type “=RIGHT(“.
Excel automatically suggests formulas as you type.
For example, you can just type “=ri” and the suggestion for “=RIGHT(” will appear.
Press the Tab key to input the suggested formula.
2. Next, input the first argument which is still cell B2.
3. Then, count the number of characters from the right to get the Extension.
You get 3, so, your formula should be:
4. Hit Enter and fill in the rest of the rows using the fill handle again.
But what if you need text from the middle part? Like the Landline number in this case.
For that, we have the MID function.
Extract text string using the MID function
The Excel MID function extracts a given number of characters starting from a specified position within the source string.
1. The first Landline number should appear in cell E2. So, type “=MID(“.
You can hide Column D.
2. The MID function has the same first input as LEFT and RIGHT which is the source text.
So, the formula becomes:
3. The second input in the MID function is start_num.
This is the starting position from where the substring will be extracted.
Let’s count each character in the Contact Number:
Type this into your formula for the start_num:
3. The third input of the MID function is num_chars. This is the number of characters from start_num that will be included in the output substring.
From the character count shown above, you know that the last digit of the Landline is at position 14.
This means the whole Landline number consists of 8 characters.
So, your formula becomes:
4. Hit Enter and fill in the rest of the rows.
You now know how to use the LEFT, RIGHT, and MID functions!
These functions are often combined with other Excel functions to produce more complex formulas.
Let’s take a look at some examples below.
Extract substring before or after a specific character
Now, take a look at the Contact Names.
We can use LEFT to extract the Title used for each person.
However, since “Mr.”/”Ms.” and “Mrs.” have different string lengths, we have to input num_chars dynamically.
If you’ll notice, the period symbol “.” is common to all titles.
You can use this as the delimiter which indicates the string length needed.
It’s now just a matter of finding that delimiter in a given text! 🔎
Example 1: Using the FIND function
The FIND function returns the position of a specified text within another text string.
1. The first Title should appear in cell F2. So, type “=FIND(“.
2. The first input in FIND is find_text. This is the character or delimiter that you are looking for.
Since we are looking for the period symbol, type:
3. Next input is within_text and this is the source string.
In this case, it is cell A2. So, the above formula becomes:
The third input is start_num. This is the starting position where Excel will look within the source string.
This input is optional and by default, FIND scans a string from the very beginning at the left.
4. Hit Enter.
5. Now, use the above FIND function as the second input in the LEFT function.
Your formula becomes:
6. Hit Enter and fill in the other rows.
The SEARCH function can also be used similarly. But it is not case-sensitive, unlike the FIND function.
Also, you can use any symbol in the FIND function as we’ll see in the next example.
Example 3: Combining FIND functions
Next, you want to extract the First Name.
This is not as straightforward as the above examples. To do this, you first have to understand the concepts below.
Let’s get to it!
You know that the First Name is between the first and second spaces ” ” which are in positions 4 and 10 respectively.
To get the First Name, you can use MID to extract characters starting from position 5 (character immediately after first space) up to position 9 (character preceding the second space).
But how exactly do you type the formula?
1. Getting the starting position for the MID function is easy enough.
You use FIND just as in the previous example. But instead of the period symbol, use the space character ” “.
Also, add 1 to the result. The starting position formula becomes:
Try this in cell G2.
2. To get the position of the second space, you need to use the third input of the FIND function which is start_num.
You know that the position after the first space can be given by:
You can then use the above formula as the starting position of the second FIND function to find the second space like below:
=FIND(” “,A2, FIND(” “,A2)+1)
Try it out in cell G2.
3. Now, you can combine the above formulas to express the string length input in the final MID function in cell G2.
Your MID formula is basically:
=MID(A2, position AFTER 1st space, position of 2nd space – position of 1st space – 1 )
So, the final formula in cell G2 will be:
=MID(A2,FIND(” “,A2)+1,FIND(” “,A2,FIND(” “,A2)+1)-FIND(” “,A2)-1)
You deduct 1 at the end to remove the extra space from the result.
4. Fill in the rows below and watch Excel magic happen!
Incredible, isn’t it? 🤩
There’s one more interesting example below!
Example 3: Using the Excel LEN function
Finally, if you want to extract the Last Name, you use the RIGHT function.
Going back to the character count, you know that the Last Name is just the characters from the right of the Contact Name up to the second space.
This length can be expressed as the length of the entire text minus the position of the second space.
You already know how to get the position of the second space from the previous example.
So, how do you get the length of the whole text string?
You can use the LEN function to get the length of any input string.
1. In cell H2, type the formula below:
2. Now, you can combine this with the formula for the second space to get the length of the Last Name:
=LEN(A2) – FIND(” “,A2, FIND(” “,A2)+1)
3. Finally, complete the RIGHT function by using the formula above as the number of characters to be extracted:
=RIGHT(A2,LEN(A2) – FIND(” “,A2, FIND(” “,A2)+1))
4. Hit Enter and fill in the rows below.
You have completed the practice workbook!
That’s it – Now what?
Now you’re well equipped to manipulate text strings using Microsoft Excel!
You can quickly extract data from thousands of records – not just contacts, but also addresses, lists, and so much more!
The formula examples we’ve shown you are just a few of the many applications you may encounter.
In fact, there are even more complex combinations of the above examples. You can combine other functions in Excel such as the SUBSTITUTE and REPT functions.
If you’d like to know more, click here to sign up for my free 30-minute email course.
There are actually several ways to manipulate text strings in Microsoft Excel.
You can use other built-in features such as Flash Fill and Text to Columns. You can learn more by watching our YouTube video on how to Split Cells in Excel – 4 Methods.
You can also do the opposite of splitting and extracting using CONCATENATE and TEXTJOIN.
I hope this Excel tutorial has helped you!
Take care! 👋