RIGHT Function

The RIGHT function extracts a given number of characters from the right side of a supplied text string. The first argument, text, is the text string to extract from. This is typically a reference to a cell that contains text. The second argument, called num_chars, specifies the number of characters to extract. If num_chars is not provided, it defaults to 1. If num_chars is greater than the number of characters available, RIGHT returns the entire text string. Although RIGHT is a simple function, it shows up in many more advanced formulas that test or manipulate text in a specific way.

RIGHT function basics

To extract text with RIGHT, just provide the text and the number of characters to extract. The formulas below show how to extract one, two, and three characters with RIGHT:

=RIGHT("apple",1) // returns "e"
=RIGHT("apple",2) // returns "le"
=RIGHT("apple",3) // returns "ple"

If the optional argument num_chars is not provided, it defaults to 1:

=RIGHT("ABC") // returns "C"

If num_chars exceeds the length of the text string, RIGHT returns the entire string:

=RIGHT("apple",100) // returns "apple"

When RIGHT is used on a numeric value, the result is text:

=RIGHT(1500,3) // returns "500" as text

Example - extract state abbreviation

The RIGHT function can be used to extract a specific number of characters from the end of a text string. For example, to extract the last two characters from "Portland, OR" you can use RIGHT like this:

=RIGHT("Los Angeles, CA",2) // returns "CA"

Of course, it doesn't make sense to extract text from a text string that you have to type into a formula. A more typical example is to work with values that already exist in cells, as seen in the worksheet below. The formula in cell D5, copied down, is:

=RIGHT(B5,2)

Notice num_chars is provided as 2 to extract the last two letters from each city and state. The result is the two-letter abbreviation for the state.

Example - extract the last character

An interesting quirk of the RIGHT function is that the number of characters to extract is not required and defaults to 1. This can be useful in cases where you only want to extract the last character of a text string, as seen below. Here, the formula in cell D5 looks like this:

=RIGHT(B5)

As you can see, without a value for num_chars, RIGHT extracts the last character from each product, which corresponds to the size.

Example - RIGHT with UPPER

You can easily combine RIGHT with other functions in Excel to get a more specific result. For example, you could nest the RIGHT function inside the UPPER function to convert the result from RIGHT to uppercase. You can see this approach in the worksheet below, where the formula in cell D5 looks like this:

=UPPER(RIGHT(B5,3))

All text in column B is lowercase. The RIGHT function extracts the state code as before and returns the result to the UPPER function, which converts the code to uppercase.

Example - RIGHT with IF

You can easily combine the RIGHT function with the IF function to create "if cell ends with" logic. In the example below, a formula is used to flag codes that end with "HNN" with an "x". The formula in cell D5 is:

=IF(RIGHT(B5,3)="hnn","x","")

As the formula is copied down, the RIGHT function returns the last 3 characters in each value, which are compared to "hnn" as a logical test. When the result is TRUE, IF returns "x". When the result is FALSE, IF returns an empty string "". The result is that the codes in column B that end with "abc" are clearly marked.

Example - RIGHT with FIND

A common challenge with the RIGHT function is extracting a variable number of characters, depending on the location of a specific character in the text string. To handle this situation you can use the RIGHT function together with the FIND function in a generic formula like this:

=RIGHT(text,LEN(text)-FIND(character,text)) // extract text after character

FIND returns the position of the character, and RIGHT returns all text to the right of that position. The screen below shows how this formula can be applied in a worksheet. The formula in cell D5 is:

=RIGHT(B5,LEN(B5)-FIND(" ",B5))

As the formula is copied down the LEN function returns the length of the text string in cell B5. Next, the FIND function returns the position of the space character " " as a number. The result from FIND is then subtracted from the result from LEN and returned to RIGHT as the num_chars argument. Finally, the RIGHT function returns all text after the space, which corresponds to the last name in this simplified example. You can read a more detailed explanation here.

Related functions

The RIGHT function is used to extract text from the right side of a text string. Use the LEFT function to extract text starting from the left side of the text, and the MID function to extract from the middle of text. The LEN function returns the length of a text string as a count of characters and is often combined with LEFT, MID, and RIGHT.

Notes

• RIGHT is not case-sensitive.
• RIGHT can extract numbers as well as text.
• The output from RIGHT is always text.
• RIGHT ignores number formatting when extracting characters.
• Num_chars is optional and defaults to 1.