Table Of Contents

arrow

RIGHT Function in Excel

Publication Date :

Blog Author :

Edited by :

Reviewed by :

Syntax

The syntax of the RIGHT function is stated as follows:

Right Function Formula

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 Characteristics of “Num_Chars”

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.

How To Use RIGHT Function in Excel?

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.

RIGHT Function Example 1

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

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

RIGHT Function Example 1-1

The RIGHT formula returns “string.”

RIGHT Function Example 1-2

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.

RIGHT Function Example 2

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

RIGHT Function Example 2-1

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

RIGHT Function Example 2-2

Example #3

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

RIGHT Function Example 3

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

RIGHT Function Example 3-1

The RIGHT function returns 111.

RIGHT Function Example 3-2

Example #4

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

RIGHT Function Example 3-3

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

RIGHT Function Example 3-4

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.

RIGHT Function Example 4

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.

RIGHT Function Example 4-1

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

RIGHT Function Example 4-2

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.

RIGHT Function Example 5

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.”

RIGHT Function Example 5-1

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

RIGHT Function Example 5-2

Example #7

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

RIGHT Function Example 6

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.

RIGHT Function Example 6-1

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

RIGHT Function Example 6-2

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

RIGHT Function Example 6-3

Example #8

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

RIGHT Function Example 7

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.

RIGHT Function Example 7-1

The formula returns the complete string in cell D3.

RIGHT Function Example 7-2

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

RIGHT Function Example 7-3

Frequently Asked Questions

#1 - What are the LEFT, RIGHT, and MID functions in Excel?

The LEFT and RIGHT functions extract characters or substrings from the left and the right side of the source string, respectively. The MID function extracts characters from the middle of the string.

The syntax of the three functions is stated as follows:

“=LEFT(text, )”
“=RIGHT(text, )”
“=MID(text, start_num, num_chars)”

The arguments are explained as follows:

- Text: The actual text string that contains the characters to be extracted. This is usually in the form of a cell reference.
- Num_chars: The number of characters to be extracted from the string.
- Start_num: The position within the source data string from where the extraction should
begin.

#2 - How to remove characters by using the RIGHT function in Excel?

The number of characters to be removed is subtracted from the total length of the string. For this, the following formula is used:

“=RIGHT(string,LEN(string)-number_of_chars_to_remove)”

The “string” is the entire source string. The “number_of_chars_to_remove” contains the number of characters to be removed from the source string.

#3 - How to use the RIGHT function for numbers in Excel?

The RIGHT function always returns a text string even if the source string is in the form of numbers. To get the output as a number from a string of numbers, the RIGHT function is nested in the VALUE function.

The formula is stated as follows:

“=VALUE(RIGHT(string, num_chars))”

The “string” is the source string containing numbers. The “num_chars” is the number of digits to be extracted from the string.

Note: In Excel, the numbers string is right-aligned while the text string is left-aligned.

  • The RIGHT function gives a specified number of characters from the right side of a text
    string.
  • The RIGHT function extracts characters beginning from the rightmost side to the left.
  • The RIGHT function accepts two arguments–“text” and “num_chars.”
  • The default value of “num_chars” is set at 1.
  • 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.”
  • The RIGHT function should not be used with numbers because it returns wrong values
    with numbers formatted as text.