Summary

The Excel RIGHT function extracts a given number of characters from the right side of a supplied text string. For example, =RIGHT("apple",3) returns "ple".

Purpose 

Extract text from the right of a string

Return value 

One or more characters.

Syntax

=RIGHT(text,[num_chars])
  • text - The text from which to extract characters on the right.
  • num_chars - [optional] The number of characters to extract, starting on the right. Default = 1.

How to use 

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)

RIGHT function example - extract state abbreviation

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)

RIGHT function example - extract last character

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))

RIGHT function example - RIGHT with UPPER

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","")

RIGHT function example - IF cell ends with

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.

RIGHT is not case-sensitive as you can see in the formula above. To perform a case-sensitive test you can combine RIGHT with the EXACT function. Example here.

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))

RIGHT function example - get last name from full name

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.

In the latest version of Excel, newer functions like TEXTBEFORE, TEXTAFTER, and TEXTSPLIT greatly simplify certain text operations and make some traditional formulas that use the RIGHT function obsolete. Example here.

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.
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.