The LEFT, RIGHT and MID functions are extremely powerful and can save you hours of work.
These functions can be used to extract portions of data from a text string in a cell. It might not make sense for just one cell, but what if you had to do it for 50 cells? Or 500?
When will I use this?
For a range of data you may want to remove unwanted characters at the start or end of each cell, you may want to split out a first name and last name into different columns, etc. But instead of doing it manually you can use a formula to automate the process.
A simple example is where each entry starts with a few random characters that you want to get rid of. A quick MID formula can strip them away in no time:
I have seen people doing this manually, with a list of hundreds of names… Don’t be that guy!
How do these formulas work?
The syntax is as follows (or feel free to skip straight to the examples below 
LEFT(text, [num_chars])
RIGHT(text, [num_chars])
MID(text, start_num, num_chars)
- text is the text string that contains the characters you want to extract (required), and num_chars is the number of characters you want to pull out.
- start_num refers to the position of the first character you want to extract.
So let’s look at a few examples!
Download the workbook here if you would like to play around with the formulas yourself.
Easy – count the characters you want to grab and skip everything else
Example 1
In the example above, we have a list of account codes made up of different elements. Let’s say the first two letters (Part A) indicate the type of customer, the next two numbers (Part B) indicates the month and the last four characters (Part C) indicate a postcode.
In row 7 we can show Part A with the formula =LEFT(B7,2) with “B7” indicating the text we are pulling information from, and “2” being the number of characters we want to grab.
We show Part B by placing the following formula in cell D7: =MID(B7,3,2) with “B7” again representing the text we are looking at, “3” being the starting position of the text we want to grab, and “2” being the number of characters we would like to extract.
Lastly we pull out Part C with the formula =RIGHT(B7,4) with “B7” indicating the text we are pulling information from, and “4” indicating we would like to grab the last four characters in that cell.
Example 2
Using the same approach we can split first names and last names into separate cells:
Let’s use the same table we used at the very start, when we removed the dodgy characters before the names.
We would like to split the names out, i.e. first names in column D and last names in column E. So we count 4 letters in “John” and 5 letters in “Smith” and split the names out as follows:
But wait a second, if we copy the formula down it gives us wonky data as all the names aren’t the same length… We could update the formulas, indicating how many characters to pick up instead:
But that seems just silly, doesn’t it? We’re still updating it manually. So let’s see if we can automate the counting as well.
Intermediate – let Excel do the counting for you
Now we add two new functions:
FIND(find_text, within_text, [start_num])
Gives you the position of a string of text within another string of text.
E.g. =FIND(“B”,”ExcelBuzz”) will return 6, as “B” is the sixth letter in the word “ExcelBuzz”.
LEN(text)
Gives you the length of a string of text.
E.g. =LEN(“Excelbuzz”) will return 9 as the string is 9 characters long.
Armed with this new information, let’s tackle those names again.
We know the first name goes all the way up to a space. So if we find the position of the space we can work out where the first name ends.
For John Smith the formula FIND(“ ”,C29) returns 5, telling us the space is in the fifth position. So to grab the name only, we take one character less, i.e. FIND(“ ”,C29)-1, being =5-1, thus 4.
Now let’s pick up this formula and stick it into our original formula (replacing the 4 we typed in manually):
=LEFT(C29,4) becomes LEFT(C29,FIND(“ ”,C29)-1):
To find the length of the surname is a bit trickier. We need to determine the length of the entire cell, then the position of the space, then subtract the one from the other.
For John Smith the formula LEN(C29) returns 10, telling us the entire cell is 10 characters long. And we already know the space is in position 5 (using the formula FIND(“ ”,C29)).
So to bring back the last name only, we combine these formulas and plug them into our original formula:
=RIGHT(C29,5) becomes RIGHT(C29, LEN(C29) - FIND(“ ”,C29)).
Now we can copy the formula down 10,000 rows and it will work beautifully every time.
If this article helped you in any way, or if you would like to add some other tips and tricks, please drop them in the comments below. And remember to share this article with anyone that might benefit from it!
