MVP #58: Using Left, Mid and Right to Extract Data From A Cell In Excel

There will be times you need to extract a portion of a cell’s entry. A practical case was a template I built for a telecoms company to determine the least cost partner to use for each international call destination. So I had to use a formula to pick out the country codes and check which provider is the cheapest to use to each destination. 

I have prepared a sample data for a simple illustration. It is the matriculation number of the university I attended. It is a clever combination of department name, year of admission and candidate number.



The first three characters are the department acronym. The two digits sandwiched between two forward slashes are the year of admission and the last four characters are the candidate number.

We are going to use LEFT to extract the department name, RIGHT to extract the candidate number and MID to extract the admission year.





It is a very easy to understand formula: =LEFT(A5,3). You simply specify the cell you want to extract from and specify the number of characters you want to extract starting from the leftmost character.

In this example, it’s three characters we want to extract starting from the left (beginning of the cell entry).

Now let’s proceed to extracting the candidate number. This time we want to extract starting from the right, four characters. So we will use RIGHT.



=RIGHT(A5,4)

Also very easy to understand.

Finally, let’s extract the admission year. It requires the MID formula. It’s a little not easy to grasp like the LEFT and RIGHT. It requires that you specify the starting point for the extraction. The concept is very easy to understand, the part that trips a lot of people up is how the starting point is determined. You have to count from the first character (from the left) to the first character you want to extract. 

In this example, we will count till the first character of the year. It is the character number 5. Then you’ll proceed to specify the number of characters you want to extract (2 in our case). 





=MID(A5,5,2)

A5 is the cell we are extracting from. 

5 is the starting point.

2 is the number of characters we want to extract.

Don't forget to register for our next special Excel Training here: In-depth Excel and Business Data Analysis Training