How to Use The String Functions LEFT, RIGHT, MID, and LEN (NEW)

How to Use The String Functions:
LEFT, RIGHT, MID, and LEN

Have you ever needed to pull just the first several digits or characters from a string in a particular cell?

text functions left right mid lenMaybe you needed just the last 4 characters in a column of cells.  Maybe you needed neither, but rather the characters or digits right smack in the middle of a string?

You are about to learn just how to do this.

Microsoft Excel offers many effective functions to allow us to manipulate data.

NEW: I've just released brand-new FREE Excel training. You learn advanced functions like IF, SUMIF, and VLOOKUP. Click to get instant (free) access!

Three of the most widely used of those are the ‘LEFT’, ‘MID’, and ‘RIGHT’ functions.

All of the examples illustrated in this article were performed using Microsoft Excel 2016 for Windows.

“The functions ‘LEFT’, ‘MID’, and ‘RIGHT’ allow you to extract substrings from an existing string of data within a given cell.”
Kasper Langmann, Creator of Spreadsheeto

We’ve created an exercise file exclusively for this tutorial.
Download it right below and get practising LEFT, RIGHT, MID and LEN. 

Click to Download This Tutorial’s FREE Exercise File

Separating what you need from what you don’t need

The ‘LEFT’, ‘MID’, and ‘RIGHT’ functions are designed to allow us to parse out substrings from a single string of data based on that substring’s relative position within the source string.

The syntax for the ‘LEFT’ and ‘RIGHT’ functions are exactly the same while the ‘MID’ function contains one additional parameter.

Let’s start by taking a look at the syntax for the ‘LEFT’ function

There are two parameters within the function: text and num_chars.

  • The text parameter simply refers to the actual string up for manipulation, most commonly just the cell reference that contains the string.
  • The num_chars parameter refers to the number of characters from the left side of the string you want to extract.

For instance, if you selected “2” for num_chars and your string was the word “excel”, your returned value would be “ex”.

Syntax of LEFT function

As we already noted, the same syntax holds true when using the ‘RIGHT’ function.

However, we are now referring to a substring relative to the right-most section of the existing string.

Referring to our example above using the word “excel” as our existing string, if you selected “2” as num_chars in your ‘RIGHT’ function, the resulting substring would be “el”.

So let’s see LEFT and RIGHT in action!

In the illustration below, we can see the results of using the ‘LEFT’ and ‘RIGHT’ functions in a worksheet.

Example of LEFT and RIGHT functions in use

Like we just discussed, the first parameter is the text string you want to parse out your substring.

In this case for row 2 in the example, we are looking for the first 2 characters of the original string which is in cell A2.

Therefore, our first parameter (text) is “A2”.

Then our next parameter, num_chars, is the number of characters from the leftmost character in the string we would like to parse out.

In this case, we want the first 2 characters of the original string so or formula will be “=LEFT(A2,2)” which gets us “ex”.

“The ‘RIGHT’ function works exactly the same as ‘LEFT’ syntactically except now we are selecting the number of characters from the end of the string we want to extract from the original string.”
Kasper Langmann, Creator of Spreadsheeto

You can see in the figure above in row 3 that the formula “=RIGHT(A3,2)” returns the substring “el” since it is the last two characters of the string “excel” found in cell A3.

Introducing a third parameter with the ‘MID’ function

Parsing out substrings from the left or the right of our string data seems easy enough, right?

So now let’s take a look at the ‘MID’ function.

‘MID’ is designed to parse out a substring from neither the left or right ends of a string, but rather a middle section of string based on the same two parameters in addition to a third.

It is clear that our starting point is understood as either the first or last character of our string when using the ‘LEFT’ or ‘RIGHT’ functions, respectively.

However, when using the ‘MID’ function we now need to supply a reference point within the parameters.

The illustration below shows the syntax of the ‘MID’ function and the third parameter, start_num:

Syntax of the function MID

The parameter start_num is the numeric position within the source data string in which you want to begin your substring extraction.

Using our earlier example of the word “excel” as our string, each letter can be thought of as a numeric position starting with 1 through 5 in this case of a string of 5 characters.

So if you wanted to parse out the middle most character of “excel”, you would select “3” as your start number and “1” as your number of characters. Assuming the data string is in cell A2, the formula would be “=MID(A2,3,1)” and our result would be the substring “c”.

Getting practical with ‘LEFT’, ‘MID’, and ‘RIGHT’

So let’s see all three functions in action together!

Below, column A contains string data for some dates. We can use the ‘LEFT’ function to extract the first three characters of the source string which in this case happens to be the day of the week.

Taking the 4 characters on the right of the source string gives us the year and if we use the ‘MID’ function starting with the fifth character of the source string and extracting the next 6 characters, we are able to parse out the month and day.

Example of LEFT, RIGHT and MID in action

This example demonstrates how easy it is to see how effective the ‘LEFT’, ‘MID’, and ‘RIGHT’ functions can be in a situation like this.

“These are very handy functions, especially when you have a scenario where the string data is consistent in length. But real life doesn’t always work out this neatly and source data is not always this cooperative.”
Kasper Langmann, Creator of Spreadsheeto

Let’s flip this whole concept around from a practical perspective for a minute. What would you do if you had strings of data that you wanted every character except the first 5 characters? No problem, right?

We can just use the ‘RIGHT’ function. But that’s assuming your entire data set contains the same number of characters and you can simply count from the right until you get to the first 5 characters of the string.

Let’s complicate the scenario a bit.

What if every string value in your data set was of different length?

How can we solve this problem since the ‘RIGHT’ function now becomes useless since our number of characters parameter is no longer consistent from one data string to the next?

“BONUS” Function: LEN

There is a very simple but extremely useful little function called ‘LEN’ that allows you to take your new found ‘LEFT’, ‘MID’, and ‘RIGHT’ function skill set up a level.

All the ‘LEN’ function does is return the length of a string in a cell.

For instance, in our earlier example where we were parsing substrings from the string “Excel”, the ‘LEN’ function applied to this string would give us 5.

Example of LEN and RIGHT

In the following example, we have strings of data on a few lines that we need to parse out just the address portion.

At first glance, you might notice that there are a consistent number of characters leading up to the address part of the string.

However, if you take a closer look you can see that the difference in the length of the street and city names makes the use of the ‘RIGHT’ or ‘MID’ functions on their own problematic.

The substring we want to parse from the string in A2 is “3704 Park Lane, Dallas 75220”. However, as you can see, each subsequent address is of varying.

Leveraging the real power of ‘LEFT’, ‘RIGHT’, and ‘MID’

Our grand solution for this will be the ‘MID’ function paired with the ‘LEN’ function to get our num_chars parameter.

Obviously, the ‘MID’ function allows us to start at the beginning of the street numbers which is consistently the 12th character into each string.

So we know two of the parameters for our ‘MID’ function. Now we need to figure out our third!

The great thing about our data strings is that the number of characters on either side of our desired substring is consistent. So if we count the number of spaces/characters from the far right to the last digit of the postal code in each string of data, we should count 57.

“Remember that our starting point for our ‘MID’ function to extract our substring was 12, so that means we are 11 spaces or characters into the original string.”
Kasper Langmann, Creator of Spreadsheeto

Put a different way, we are essentially eliminating the first 11 characters or spaces of our original string to make sure our substring begins with the 12th character.

Because of this, we need to add 11 to the 57 we counted from the right in order to compensate. Subtracting this number from the length, or ‘LEN’, of each string gives us the exact number of characters we want to parse out. So the formula for A2 should look like “=MID(A2,12,LEN(A2)-68)”.

MID with LEN

This same concept using ‘LEN’ works well with both the ‘LEFT’ and ‘RIGHT’ functions also when you need to parse out substrings of varying lengths (so long as the rest of the string to the right or left, respectively, are the same length across each data string).

Consider the previous example. Say you wanted to extract the same substring we parsed out but instead of starting with the street number you wanted to start at the beginning of the string.

Then your formula would simply be “=LEFT(A2,LEN(A2)-57)”.

So now you can see not only the general usefulness of the ‘LEFT’, ‘MID’, and ‘RIGHT’ functions in relatively simple scenarios, but also the real power of the functions in more complicated situations with varying string lengths across a data set.

It’s amazing how adding a simple little function like ‘LEN’ to the mix can level up your new found data parsing skills to create an effective solution to an otherwise time consuming and arduous task.

“The ‘LEFT’, ‘MID’, and ‘RIGHT’ functions are a few of the most indispensable Excel functions you will ever learn and they are profoundly powerful in their simplicity.”
Kasper Langmann, Creator of Spreadsheeto

We’ve created an exercise file exclusively for this tutorial.
Download it right below and get practising LEFT, RIGHT, MID and LEN. 

Click to Download This Tutorial’s FREE Exercise File

2018-03-28T10:30:51+00:00

Send this to a friend