RIGHT Function in Excel

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:

• 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 the 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 to 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 to 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.

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 to 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 to 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 to 9.

FIND(“x”, B4) + 1

since “x” is followed by a space, we can add one to omit the space. It will return to 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 in one place after “x.”

The above RIGHT Formula in Excel will return “150 ft”. You may drag the cursor and get the result for the 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 to 17.

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

will give the number of characters occurring to the right of “@.” It will return to 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 the 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 a forward slash “/,” the last character is omitted else, it returns the complete string.

Similarly, you can do so for the 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 are greater than the length of the text.
• If num_chars is omitted, it takes the default value of 1.

Recommended Articles

This has been a guide to RIGHT Function in Excel. Here we discuss the RIGHT Formula in excel and how to use the RIGHT Excel function along with excel example and downloadable excel templates. You may also look at these useful functions in excel –

• RIGHT Formula in Excel
• VBA Left Function
• RIGHT Function in Excel VBA
• Access vs. Excel
• Null in Excel