Excel RIGHT Function – How To Use

RIGHT function is one of the string manipulation functions of Excel. The RIGHT function returns the stated number of characters from the end of a provided text string. If you wish to extract characters from the extreme right of the string, specify the number of characters in the formula, and it will pull those characters from right to left.

The RIGHT function will come in very handy when you wish to extract pin codes or state names from addresses, fetch last few digits from a number to perform a divisibility test, or extract last name from the list of full names.

Let's explore the RIGHT function in detail and how it can be combined with other Excel functions to create powerful formulas.

Excel RIGHT Function

Syntax

The syntax of the RIGHT function is as follows:

=RIGHT(text,[num_chars])

Arguments:

'text'- It is a mandatory argument. The input value can either be a text in double quotes or a reference to a cell having the string.
'num_chars'- This is an optional argument. The num_chars argument indicates how many characters should be returned from the end of the input text (right to left). If it is left blank, the default value is 1.

Important Characteristics of the RIGHT function

  • The most noteworthy characteristic of the RIGHT function is that it is a text function which means irrespective of the type of input value (in the text argument), the return value will always be in text format.
  • If the text argument contains a string or text not enclosed in double quotes, you will get a #NAME? error.
  • If num_chars is not numeric, the function returns a #VALUE! error.
  • If the value of the num_chars argument is greater than the total text length (text argument), the entire text will be returned.
  • If num_chars is not an integer, the function will round the number down to the nearest lower integer and return the relevant characters.
  • If the num_chars value is less than zero, the RIGHT function will return a #VALUE! error.

Examples of RIGHT Function

The best way to understand the usage and utility of the RIGHT function in Excel is by examples. It can be used with other functions such as SEARCH, FIND, LEN, VALUE, and many more. Let's understand its basic functionality before we pair it up with further examples.

Example 1: Basic Use

To easily comprehend the primary use of the RIGHT function, we have taken 4 different instances. Let's examine the function's behavior with text, space, special characters, and numbers.

RIGHT function in Excel with examples

In the 1st example, the input value C4 contains a sentence 'Basic use of RIGHT function'. We want to extract the last 8 characters of this sentence. As per the syntax, the formula used is as follows:

=RIGHT(C3,8)

As we wish to pull 8 characters from right to left, num_chars will be 8. The last 8 characters are 'function', hence the return value (as seen in cell E3).

The second instance shows how the RIGHT function behaves with special characters. Cell C4 contains the input string "$pec!al Chârâctër", which includes various special characters. We want to extract 10 characters from this text. The formula will be as follows:

=RIGHT(C4,10)

The return value is in cell E4, which is 'Chârâctër"'. This simply depicts that all special characters, are counted when using the RIGHT function.

In the third scenario, the input value in cell C5 is numerical, which is 123456789. We want to pull the last 3 characters from the input value. The formula used is as follows:

=RIGHT(C5,5)

The return value of the function used is 789, as seen in cell E5. But is it a numerical value? The RIGHT function is categorized as a text function; will it behave differently with numbers? Keep reading and find the detailed explanation in the further section.

Lastly, in the fourth case, we have skipped mentioning the num_chars argument. The formula used is

=RIGHT(C6)

So, the first argument is the cell reference to the input text C6. As the second argument is optional, we have left it blank. The default value of the num_chars argument is 1. The return value will be the first character from the right, i.e., 'e' as seen in cell E6.

Example 2: Extract substring after a special character

Imagine a data set where you want to extract a part of the string from an input text after a special character.

For example, extract domain names from the email address or street names from the home address. You can ordinarily use the RIGHT function alone when you know the specific number of characters to be extracted; however, this is impossible in cases like our example. The character length for each domain varies. In such a scenario, the FIND and LEN functions can help you.

The issue is that the number of characters after the special character might differ in each case, and you will not have a fixed integer value for the num_chars argument. But the problem will be solved if there is a special character splitting the substring and the remaining text.

In that case, the LEN function will determine the total length of the string, and the FIND function or SEARCH function will extract the position of the particular character before the substring. You can then subtract the character's position from the total length to determine the number of characters after the special character, i.e., your substring's length.

Let's use an example to show how to use the RIGHT function to extract a substring that comes after a special character with the LEN and FIND functions.

In this example, the data set here contains the name of people along with their email addresses. We want to extract the domain name from the email address. As you can see, each email address includes a username and domain name separated with a special character – @. (E.g., [email protected], where example is the user's name and exceltrick.com is the domain name combined with @).

Extract substring after a special character

The email addresses are in column C. The total length of the email address in cell C2 ([email protected]) can be determined using the formula LEN function, which in this case is 22.

Now, to find the position of the special character – @, we will use the find function as:

=FIND("@",C2)

The result in this case comes out as 13. Now we know that the total length of the email ID in cell C2 is 22 and '@' is in the 13th position.

To extract the domain name after '@', subtract the position of '@' from the total length (22-13), which is 9. If we use the RIGHT function with the cell reference of the email ID as the first parameter(text) and the subtracted value as the second parameter (num_chars), we will have our domain name.

The final formula will be as follows:

=RIGHT(C2,(LEN(C2)-FIND("@",C2)))

Extract substring after a special character

Example 3: Remove the first N characters from a string

RIGHT function is usually used to extract a specific number of characters from the end of a string. However, it can also be used to remove a few characters from the beginning of a text.

In this example, you have a list of top movies. You want to remove the beginning of each text 'Movie N' and extract the movie name only.

Remove the first N characters from a string

We will not be able to use the RIGHT function directly as the number of characters in each movie name varies. Neither can we use the SEARCH or FIND function, as there is no special character joining the prefix with the movie name. So, we use a combination of LEN and RIGHT functions.

The idea is to subtract the number of characters to be removed from the total length of the string/text. Then use that value as the num_chars argument.

In this example, column C contains the movie names and each name has a prefix of 7 characters – Movie with a space and a number. If we remove that prefix, we will have our list of movie names.

Remove the first N characters from a string

Cell B3 contains the first movie – 'Movie 1Godfather'. The total string length can be calculated using the LEN function, which is 16 in this instance. Subtracting 7 (characters to be removed from the beginning) from 16 (total length) will give us the final value of the num_chars argument of the RIGHT function.

In each case, the total characters minus the fixed initial 7 characters will result in the number of characters to be returned from the right side of the text string. The final formula is as follows:

=RIGHT(B3,LEN(B3)-7)

RIGHT Function with Date

We know that, dates in Excel present themselves as dates due to cell formatting. Originally, Excel treats them as serial numbers. For Excel, January 1st, 1900 is equal to 1, and it continues to add as we add a day. So, January 2nd, 1900 would be 2 and so on.

If you try to use the RIGHT function with dates, it will extract the last few characters of the serial number corresponding to that date. Refer to the example below for a better understanding.

In the example below, we want to extract the last 4 characters from the date in cell C3, which is 26-01-2022.

Theoretically, using the following formula should extract 2022.

=RIGHT(C3,4)

RIGHT Function with Date

But, as you can see in cell D3, the formula returns 4587. This is because, in Excel, the date 26-01-2022 is internally stored as a serial number 44587. Therefore, the last 4 characters of the serial number are 4587 and this is the result that has been returned by the function.

Then, how to use the RIGHT function with dates? The solution is to use the date as text input. There are two ways to do that.

The first solution is using the date directly as the text argument. So, the formula used will be as follows:

=RIGHT("26-1-2022",4)

RIGHT Function with Date

The date 26-1-2022 is used as the first argument and since we wish to extract 4 characters from right, we are using 4 in the second argument. In such a case the function returns 2022 as the result.

This is easy for one instance since you have to feed the date into the formula but for more instances, things are already looking tiresome.

The second option is to use the date in text format and then input the cell reference value. To convert the date to text, you can simply use the TEXT function which converts a number to a given format.

Cell B3 contains the date 26-01-2022 in date format. To convert it into text format, the formula used is as follows:

=TEXT(B3, "dd-mm-yyyy")

This includes the cell reference of the date that needs to be converted along with the date format. The return value is in cell C3. It is important to note here that although both the dates in cells B3 and C3 look the same, but they have different underlying datatypes.

Excel uses right alignment for numbers and left alignment for text. Since, the text in the cell C3 is left-aligned, therefore it is in text format.

RIGHT Function with Date

Now, use cell C3 as an input value in the RIGHT function to extract the year. The formula used will be as follows:

=RIGHT(C3,4)

Extracting 4 characters from right to left from cell C3 containing the date – 26-01-2022 gives us the desired result – 2022 as seen in cell D3.

RIGHT Function with Date

Forcing RIGHT Function to Return Numbers

Since the RIGHT function is a text function, whatever value it returns will be in the text/string format. This can be better explained using an example.

Here, we are multiplying 500 by 501 and then extracting the last 3 digits of the returned value. Ideally, the return value must be numerical as all the input values are numbers. But that's not the case.

Forcing RIGHT Function to Return Numbers

We're attempting to multiply two values and then using the RIGHT function to pull out the last 3 characters. Consequently, the formula used is as follows:

=RIGHT(500*501,3)

As per basic mathematical calculation, the resultant of 500*501 is 250500. If we extract 3 characters from right, the result will be 500.

Now, an important observation to be made here is that the return value in column C is in text format, evident from the left-alignment.

Another way to confirm that the RIGHT function returns a text value is by using the ISNUMBER function.

Forcing RIGHT Function to Return Numbers

The output is FALSE. This clearly depicts that the result from RIGHT function is not a number.

So, how to ensure that the RIGHT function returns a number and not a text value?

Use the VALUE function, which alters a number from text or string to numerical format. Combining the VALUE and RIGHT functions will take up the return value of the RIGHT function and convert it to numerical datatype.

The formula used will be as follows:

=VALUE(RIGHT(500*501,3))

Forcing RIGHT Function to Return Numbers

Note that the return value in cell C20= where we use the VALUE and RIGHT function is right-aligned, which means it's a number. Furthermore, the result is TRUE when checked using ISNUMBER function.

RIGHT Function vs LEFT Function vs MID Function

You now understand the different ways in which Excel's RIGHT function can be used. However, if you wish to extract characters from the left or middle of the string, you can use the LEFT function or the MID function, respectively.

In the following example, we will try to use all three functions on the same input string for better comprehension.

The input text in cell A2 is "Smart, Easy & Effective"

RIGHT Function vs LEFT Function vs MID Function

When using the LEFT function to extract the first word from the left, we use the following formula:

=LEFT(A2,5)

As the first word is 5 characters long. The result is "Smart".

Now, in the second scenario, we want to extract the words 'Easy', which is the middle of the sentence. Using the MID function, the formula will go as:

=MID(A2,8,4)

Wondering, what's the additional parameter?

The additional parameter is the position from where the extraction must begin. As the first word is 'Smart', which is 5 characters long followed by a comma and a space, this parameter becomes 8.

Lastly, the function that you know very well. The RIGHT function is used to extract the last word of the sentence. The formula used is as follows:

=RIGHT(A2,9)

That's all you need to know about this nifty Excel function. Try all these examples on your own, and by the time you get comfortable with these formulas, we'll pull out another Excel trick from our hat.

Compiled by - Content Studio

Thanks for reading. If you have found this article helpful show your love by sharing it with your friends & colleagues. All the tutorials on the Excel Trick are produced, reviewed, and fact-checked by a team of experts. You can check out our team here.