RIGHT Function in Excel

Similar to Left function in excel, Right function is also a text function which is used to give the number of characters from the end from the string which is from right to left, for example if we use this function as =RIGHT ( “ANAND”,2) this will give us ND as the result, from the example we can see that this function takes two arguments. RIGHT formula in excel is used to return the last character or characters in a supplied text string, based on the number of characters specified.

RIGHT Formula in Excel

Explanation

RIGHT in Excel Arguments:

text: The text string containing the characters to extract.

num_chars: Optional. The number of characters to extract from the text starting from the right. Default =1

The default value of num_chars is 1, and it must be greater than or equal to zero. If num_chars is greater than the length of text, the RIGHT Excel function returns the complete text. The RIGHT function in excel should not be used with numbers. Though it can perform the same operation on digits as it does with text, it returns wrong values with number formatted text.

How To Use RIGHT Function in Excel?

The RIGHT Excel function is mostly used along with other Excel functions such as FIND, SEARCH, LEN, LEFT, etc. Some of its uses are:

You can download this RIGHT Function Excel Template here – RIGHT Function Excel Template

RIGHT in Excel is used to remove the trailing slash in URLs
RIGHT in Excel is used to obtain the domain name from the email address.
RIGHT in Excel is used in text formatting
RIGHT in Excel is used to obtain the last name
RIGHT in Excel is used to obtain text occurring after a specific character.

Example #1

Suppose you have a string in A3 as shown below and you want to extract the last word having six letters.

You can use the RIGHT Formula in Excel to extract “string” in A3:

RIGHT(A3, 6)

The above RIGHT Formula in Excel will return “string”

Example #2

Suppose you have a list of IDs such as ID2101, ID2102, ID2103 etc. in a column as shown below.

In this, the actual ID is last four digits which are unique, and the word “ID” is redundant. You want to remove “ID” from each of the identifiers. Since we want only the last four digits, the following RIGHT Formula in Excel will do the job:

RIGHT(A3, 4)

The RIGHT Formula in Excel will return 2101.

Example #3

Suppose you have a 6 digit number (140111) and you want to extract the last 3 digits from this number.

You can use the following RIGHT Formula in Excel to extract the last three digits:

RIGHT(A4, 3)

The RIGHT Excel function will return 111.

However, if you have a Date instead of a simple number and want to extract the last few digits, the RIGHT Excel function will not give you the exact answer.

Suppose you have a random date 12/07/2018 in a cell A5.

You want to extract the last three digits. Let us try using the RIGHT Formula in Excel used above

i.e., RIGHT(A5, 3)

The RIGHT Excel function will return 293 instead of 018. This is because it takes the original value of the date and then gives the output.

Popular Course in this category

All in One Excel VBA Bundle (35 Courses with Projects)4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion

View Course

Example #4

Suppose you have a combination of two animals, separated by “,” and space as shown below.

Now, you want to extract the last name. You can do so using the RIGHT Formula in Excel:

RIGHT(A4, LEN(A4)-FIND(“ ”, A4))

FIND(“ ”, A4)

will find the location where space is occurring. This will return 5. You may alternatively use “, ” for a stricter search.

LEN(A4)

will calculate the length of the string “Dog, Wolf” and will return 9

LEN(A4)-FIND(“ ”, A4)

will return the position of space from the right. It will return 4.

RIGHT(A4, LEN(A4)-FIND(“ ”, A4))

will return four letters from the right of the string A4.

The output for the above RIGHT Formula in Excel will be “Wolf”.

Example #5

Suppose you have two-dimensional data, such as length x width, in a single cell as shown below.

Now, you want to extract only the width from the given dimension. For the 1^st dimension, you can use the RIGHT Formula in Excel:

RIGHT(B4, LEN(B4)- (FIND(“x”, B4) + 1))

Let us look at the RIGHT Formula in Excel in detail:

FIND(“x”, B4)

will give the location of “x” in the cell. It will return 9.

FIND(“x”, B4) + 1

since “x” is followed by a space, we can add one to omit the space. It will return 10.

LEN(B4)

will return the length of the string.

LEN(B4)- (FIND(“x”, B4) + 1)

will return the number of the characters occurring after “x” +1.

RIGHT(B4, LEN(B4)- (FIND(“x”, B4) + 1))

will return all the characters occurring at one place after “x”.

The above RIGHT Formula in Excel will return “150 ft”. You may drag the cursor and get the result for rest of the cells as well.

Example #6

Suppose you have a list of email addresses and you want to obtain the domain names from these email ids.

You can use the following RIGHT Formula in Excel to extract the domain name for the first email address.

RIGHT(C3, LEN(C3) – FIND(“@”, C3))

FIND(“@”, C3)

will find the location where “@” is occurring in the string. For C3, it will return 7.

LEN(C3)

will give the length of the string C3. It will return 17.

LEN(C3) – FIND(“@”, C3)

will give the number of characters occurring to the right of “@”. It will return 10.

RIGHT(C3, LEN(C3) – FIND(“@”, C3))

will give the last 10 characters from C3.

The above RIGHT Formula in Excel will return “amazon.com”.

Similarly, you can do so for rest of the cells.

Example #7

Suppose you have some URLs and you want to remove the last backslash from the URLs.

You can use the following RIGHT Formula in Excel:

=LEFT(C3, LEN(C3) – (RIGHT(C3)=”/” ))

RIGHT(C3)=”/”

The RIGHT Excel function takes a default of one, i.e., the last value. If the last character is a forward slash “/”, it will return TRUE else FALSE. These TRUE and FALSE changes to 1 and zero.

LEN(C3) – (RIGHT(C3)=”/” ))

If the last character is a forward slash “/”, one is subtracted from the length of the string. This means that the last character is omitted if it is “/”.

=LEFT(C3, LEN(C3) – (RIGHT(C3)=”/” ))

It will return the first n number of characters as specified by the above syntax. If there is forward slash “/”, the last character is omitted else it returns the complete string.

Similarly, you can do so for rest of the cells.

Things to Remember

The function is used to obtain the characters occurring to the right of a specified location
If the characters to the left are required, the LEFT function is used.
Num_chars must be greater than or equal to zero. If the num_chars is greater than zero, it gives #VALUE! error.
The RIGHT Excel function returns the complete text if the num_chars is greater than the length of text.
If num_chars is omitted, it takes the default value of 1.

RIGHT Function in Excel