RIGHT Function in Excel

The syntax of the RIGHT function is stated as follows:

The RIGHT function accepts the following two arguments:

• Text: The text string that contains the characters to be extracted.
• Num_chars: The number of characters to be extracted from the string starting from the rightmost side.

The “text” is a mandatory argument and “num_chars” is an optional argument.

The features of “num_chars” are listed as follows:

• The default value of “num_chars” is set at 1. This means that if the value of “num_chars” is omitted, the RIGHT function returns the last letter of the string.
• If “num_chars” is greater than the length of the text, the RIGHT function returns the complete text.
• If “num_chars” is less than zero, the RIGHT function returns the “#VALUE! error.”

Note: The RIGHT function should not be used with numbers. Though it performs the same operation on digits as it does with text, it returns wrong values with numbers formatted as text.

The RIGHT function is mostly used in combination with other Excel functions like FIND, SEARCH, LEN, LEFT, etc.

The uses of the RIGHT function are listed as follows:

• It removes the trailing slash in URLs.
• It extracts the domain name from the email address.
• It helps format text.
• It extracts text occurring after a specific character.

Example #1

A text string is given in cell A3, as shown in the succeeding image. We want to extract the last word having six letters.

Let us apply the RIGHT formula to extract “string” in A3.

We use “=RIGHT(A3,6).”

The RIGHT formula returns “string.”

Example #2

The following image shows a list of IDs like “ID2101,” “ID2102,” “ID2103,” etc., in column A.

Here, the last four digits of the ID are unique and the text “ID” is redundant. So, we want to remove “ID” from the list of identifiers.

We apply the formula “=RIGHT(A3,4).”

The RIGHT function returns 2101 in cell B3. In the same way, we extract the last four digits of all the IDs.

Example #3

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

We apply the formula “=RIGHT(A4,3)” to extract the last three digits.

The RIGHT function returns 111.

Example #4

We have the date “12/07/2018” in cell A5. We want to extract the last few digits.

To extract the last three digits, we apply the formula “=RIGHT(A5, 3).”

The function returns 293 instead of 018. This is because it considers the original value of the date before returning the output. So, the RIGHT function does not give the correct answer in this case.

Example #5

We have the names of two animals in column A. The names are separated by a comma and space, as shown in the following image. We want to extract the last name.

We apply the following RIGHT formula.

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

The “FIND(“ ”,A4)” finds the location of the space. It returns 5. Alternatively, we can use “,” for a stricter search.

The “LEN(A4)” calculates the length of the string “Dog, Wolf.” It returns 9.

The “LEN(A4)-FIND(“ ”,A4)” returns the position of the space from the right. It returns 4.

The formula “RIGHT(A4,LEN(A4)-FIND(“ ”,A4))” returns four letters from the right of the text string in A4.

The output of the RIGHT formula is “Wolf.” Similarly, we find the output for the remaining cells as well.

Example #6

We have two-dimensional data, the length multiplied by the width, as shown in the following image. We want to extract the width from the given dimensions.

For the first dimension, we apply the following RIGHT formula.

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

The “FIND(“x”,B4)” gives the position of “x” in the cell. It returns 8.

The formula “FIND(“x”,B4)+1” returns 9. Since “x” is followed by a space, we add one to omit the space.

The “LEN(B4)” returns the length of the string.

The “LEN(B4)-(FIND(“x”,B4)+1)” returns the number of characters occurring after “x”+1.

The formula “RIGHT(B4,LEN(B4)-(FIND(“x”,B4)+1))” returns all the characters occurring in one place after “x.”

The RIGHT formula returns “150 ft” for the first dimension. Drag the fill handle to get the results for the remaining cells.

Example #7

We have a list of email addresses. We want to extract the domain name from these email IDs.

We apply the following RIGHT formula to extract the domain name from the first email address.

“=RIGHT(C3,LEN(C3)-FIND(“@”,C3))”

The “FIND(“@”,C3)” finds the location of “@” in the string. For cell C3, it returns 7.

The “LEN(C3)” gives the length of the string C3. It returns 17.

The “LEN(C3)-FIND(“@”,C3)” gives the number of characters occurring to the right of “@.” It returns 10.

The “RIGHT(C3,LEN(C3)-FIND(“@”,C3))” gives the last 10 characters of cell C3.

The RIGHT formula returns “amazon.com” in cell D3.

Similarly, we apply formulas to the remaining cells as well.

Example #8

We have some URLs shown in the following image. We want to remove the last backslash from these URLs.

We apply the following formula.

“=LEFT(C3,LEN(C3)-(RIGHT(C3)=”/”))”

By default, the RIGHT function returns one value on the rightmost side, i.e., the last value.

If the last character is a forward slash (/), “(RIGHT(C3)=”/”)” returns “true,” else it returns “false.” These “true” and “false” change to one and zero, respectively.

If the last character is a forward slash (/), one is subtracted from the length of the string. This means that if the last character is “/”, the “LEN(C3)-(RIGHT(C3)=”/”))” omits it.

The “=LEFT(C3,LEN(C3)-(RIGHT(C3)=”/”))” returns the first “n” number of characters. If the last character is a forward slash (/), it is omitted; else, the complete string is returned.

The formula returns the complete string in cell D3.

Similarly, we apply formulas to the remaining cells as well.