RIGHT Function in Excel

RIGHT in Excel (Table of Contents)

• RIGHT Function in Excel
• RIGHT Formula in Excel
• How To Use RIGHT Function in Excel?

RIGHT Function in Excel

RIGHT 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

Recommended Courses

Explanation of RIGHT Function in Excel

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

RIGHT Function in Excel 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”

RIGHT Function in Excel 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.

RIGHT Function in Excel 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.

RIGHT Function in Excel 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”.

RIGHT Function in Excel 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.

RIGHT Function in Excel 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.

RIGHT Function in Excel 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 About RIGHT Function in Excel

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

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

Recommended Articles

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

• Excel Formulas Cheat Sheet
• RAND Excel Function
• INDIRECT Function in Excel
• GetPivotData Function in Excel
• Column Function in Excel
• Hyperlink Excel Function
• Access vs Excel