Using Excel's RIGHT function

The RIGHT function in Excel is an extremely common — and simple — string manipulation tool. RIGHT is used to return a chosen number of characters that fall on the rightmost side of a string. Use it when you want to extract data that lies on the right-hand side of a string and eliminate everything else.

Using RIGHT

The syntax for the RIGHT formula is as follows:

=RIGHT(text, num_characters (optional))

When you call RIGHT, the function will examine your text argument and only return the rightmost num_characters characters in that string. For example, the following function will return only the rightmost two characters of a given string:

=RIGHT("Boston, MA", 2)
Output: "MA"

As you can see, RIGHT is particularly helpful when extracting the names of states from a City, State pair. Here are a couple more examples of the RIGHT function used to extract location information:

=RIGHT("San Francisco, CA", 2)
Output: "CA"

=RIGHT("Chicago, IL", 2)
Output: "IL"

=RIGHT("Austin, USA", 3)
Output: "USA"

Using RIGHT without a num_characters argument

The num_characters argument within RIGHT is technically optional. If you leave it off, the function will return the rightmost single character in the specified string, like so:

=RIGHT("Austin, USA")
Output: "A"

There are very few situations in which extracting the rightmost single character from a string of text is useful, so we usually end up using RIGHT with a num_characters argument.

Extracting the rightmost variable text strings

Using RIGHT with a static num_characters like 2 is perfectly useful if we know the size of the data we're trying to extract (for example, when we know the rightmost two characters in a string will always represent a state abbreviation). However, what happens when we don't know the length of the string we want to pull?

Let's say, for example, that we want to extract the state names from the strings "Boston, Massachusetts" and "San Francisco, California". We can't use a static num_characters, because "Massachusetts" and "California" have different numbers of characters:

This is where things get fancy: we can combine the RIGHT function with LEN, SEARCH, and TRIM to perform our extraction.

Take a look at the following common formulation:

=TRIM(RIGHT(B3, LEN(B3)-SEARCH(",", B3)))
Output: "Massachusetts"

Whoa there! That's a complicated formula, but it came out with the correct answer: "Massachusetts". Let's break it down to see what's happening.

First, SEARCH look for the first occurence of the "," character in cell B3, finding it at position 7. Then, we take the LEN of the entire B3 string, 21, and subtract 7 to find the number of characters to the right of the first ",": 14. Finally, we use the RIGHT function on the whole phrase, pulling the rightmost 14 characters, and TRIM the result to eliminate the unnecessary leading space. The result is the state name we're looking for: "Massachusetts". Here's the whole formula broken down step by step:

=TRIM(RIGHT(B3, LEN(B3)-SEARCH(",", B3)))
Step 1: =TRIM(RIGHT(B3, LEN(B3)-7))
Step 2: =TRIM(RIGHT(B3, 21-7))
Step 3: =TRIM(RIGHT(B3, 14))
Step 4: =TRIM(" Massachusetts")
Output: "Massachusetts"

Note that this same formula works on the other cells in the table, because we are using functions to determine a variable num_characters for the RIGHT function:

=TRIM(RIGHT(B4, LEN(B4)-SEARCH(",", B4)))
Output: "California"

There you go! There are many more ways to combine RIGHT with other string functions, but the above formulation is one of the most common. Try to experiment with some other ways to use RIGHT to pull text you want out of a given string.

Comments