Exceljet

Quick, clean, and to the point

How to extract text with LEFT and RIGHT

In this video, we'll look at how to use the RIGHT and LEFT functions to extract specific text from data.

Let's take a look.

Here we have some customer data. To illustrate how to extract text using the LEFT and RIGHT functions, I'll use them both to pull out just certain parts of this information.

For the first example, I'll extract the area code from the phone number. This is a perfect application for the LEFT formula. LEFT takes two arguments, the text you're working with and the number of characters to extract, which is optional.

If I use LEFT without the second argument, it will extract just the first character.

To get the area code, I'll need to use 3 for the second argument.

For the next example, I need to extract the zip code from column E. In this case, the zip codes are all exactly 5 digits, so the problem is basically the reverse of the area code problem - we just need to extract 5 characters starting from the right instead of the left. For that, we can use the RIGHT function.

Like LEFT, RIGHT takes just two arguments, the text, and the number of characters to extract, which is optional.

Without the 2nd argument, right will extract just the first character on the right.

To get the zip code, I'll need to use 5 for num_chars.

Finally, we need to get the state from the values in column E. This is tricker, because we can't extract the state with either RIGHT or LEFT alone. State appears in the middle of the text, between city and zip code.

One way we can handle this problem is to take it in two steps.

In step one, we'll grab the state and the zip code using the RIGHT function along with num_chars equal to 8. We can do this because state and zip code with a space character is always 8 characters in length.

In step two, we'll wrap the RIGHT function inside the LEFT function and pop off the first two characters from the left. Now the formula returns just the state.

This is a good example of how and why formulas are nested. You'll often need to use one function to solve part of a problem, then feed the result into another function to finish the work.

Course 
Core Formula
Author 
Dave Bruns