How to extract text with LEFT and RIGHT (video)

Abstract

In this video we look at how to use the RIGHT and LEFT functions to extract one or more characters from the beginning or end of a string.

Transcript

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 of the LEFT function. 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 D. In this case, the zip codes are all exactly five digits, so the problem is basically the reverse of the area code problem; we just need to extract five characters starting from the right instead of from the left. For that, we can use the RIGHT function.

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

Without the second argument, RIGHT will extract just the first character from 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 D. This is trickier, 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 zip code using the RIGHT function with the second argument of "8". We can do this because state and zip code with a space character is always eight characters in length.

In step two, we'll wrap the RIGHT function inside the LEFT function and pop off just 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 the problem, and then feed the result into another function to finish the work.