RIGHT is another popular function in MS Excel that is used to get the last character or characters in a text string, based on the specific number of characters. In one word, this function is designed to return a specified number of characters from the rightmost side of a string. This article will share the complete idea of how the RIGHT function works in Excel independently and then with other Excel functions.
Download the Practice Workbook
RIGHT Function in Excel (Quick View)
Excel RIGHT Function: Syntax & Arguments
=RIGHT (text, [num_chars])
|Argument||Required or Optional||Value
|text||Required||Pass the text from which to extract characters on the right.|
|[num_chars]||Optional||Pass the number of characters to extract, starting on the right. The default value is 1.|
- If num_chars is not provided, it defaults to 1.
- If num_chars is greater than the number of characters available, the RIGHT function returns the entire text string.
- RIGHT will extract digits from numbers as well as text.
- This function does not consider the formatting of any cell. Like a date, currency, etc.
How to Use the RIGHT Function in Excel (6 Examples)
Example 1: How to Get a Substring that Comes after a Certain Character
Let’s assume we have a dataset of customers with their Name, Order ID, Address, Total Price. Now we will extract the last name of each customer from their full name using the RIGHT function.
Step 1: Enter the formula in cell C4
- SEARCH(” “, B4) this portion finds the space from the Full name cells.
- Then LEN(B4)-SEARCH(” “, B4) this portion will select the last part of the name.
- Then the RIGHT function will return the selected portion.
Example 2: How to Extract a Substring after the Last Occurrence of the Delimiter
Now consider we have a dataset of customer comments. There in each comment, there is a comments number like Comments 1, Comments 2, etc. Now our task is to extract the only comments from the source comment.
Step 1: Enter the formula in cell D4 and copy up to D11
- LEN(SUBSTITUTE(C4,”:”,””)) this portion finds the colon (:) sign in the whole string.
- SUBSTITUTE(C4,”:”,”#”,LEN(C4)-LEN(SUBSTITUTE(C4,”:”,””))) this part replaces the last delimiter with some unique character.
- Then SEARCH(“#”, SUBSTITUTE(A2,”:”,”#”,LEN(A2)-LEN(SUBSTITUTE(A2,”:”,””)))) this part gets the position of the last delimiter in the string. Depending on what character we have replaced the last delimiter with, use either case-insensitive SEARCH or case-sensitive FIND to determine the position of that character in the string.
- Lastly, the RIGHT function selected comments and print them.
Example 3: How to Remove the First N Characters from a String Using RIGHT Function
The above task can be done using a simple formula. As there is a fixed number of characters “Comment N” which is 10 in the first portion of each comment, we can easily eliminate it and get the comment only. Here we will remove the first 10 characters from the Source Comment and print the only comments in a separate column.
Step 1: Enter the formula in cell D4 and copy it down up to D11
- LEN(C4)-10 this will return a number after subtracting 10 from the total characters number. If the total length is 25 then this portion will return 25-10 = 15
- Then the RIGHT function will return the only comment from the source comment.
Example 4: Extract Number from a String Using RIGHT and VALUE Function
The RIGHT function does not allow the return of a number from any string. It returns the number in text format. But using the VALUE and RIGHT functions we can return numbers in the correct format. Here we will use the same dataset above and we will extract the ZIP Code in number format from the Address column.
Step 1: Enter the formula in cell F4 and copy it down up to F11
- RIGHT(E4, 5) this portion gives the 5 characters from the address which is zip code in text format.
- Then VALUE function converts them into number format.
Example 5: Extract Domain Name from Email using RIGHT Function
Let’s have a customer dataset with their Order ID, Name, Email, and Address. Now we will find out their email domain from the given email address using the RIGHT function.
Step 1: Enter the formula in F4 and copy it down up to F11
- FIND(“@”,E4) this portion find @ from the given string.
- LEN(E4)-FIND(“@”, E4) this will give the number up to which the value will be extracted.
Example 6: Modify URL Using RIGHT Function
This RIGHT function also helps us to modify any type of URL. Let’s in our dataset we have several URLs of some websites. Now in some of them is a backslash / in the URL. Now our task is to find out those URLs and remove this backslash from the URL.
Step 1: Enter the formula in cell C4 and copy it down up to C8
- If the last character is a forward slash (/), (RIGHT(B4)=”/”) returns “true,” else it returns “false.”
- The =LEFT(B4, LEN(B4)-(RIGHT(B4)=”/”)) returns the first “n” number of characters. If the last character is a forward slash (/), it is omitted; else, the complete string is returned.
User Problems with RIGHT Function
- Can the Excel RIGHT function return a number?
As mentioned at the beginning of this tutorial, the RIGHT function in Excel always returns a text string even if the original value is a number.
- Why doesn’t the RIGHT function work with dates?
Since the Excel RIGHT function is designed to work with text strings whereas dates are represented by numbers in the internal Excel system, a RIGHT function is unable to retrieve an individual part of a date such as a day, month, or year. If you attempt to do this, all you will get is a few last digits of the number representing a date.
Things to Remember
|Common Errors||When they show|
|#VALUE!||If “num_chars” is less than zero, the RIGHT function returns this error.|
That’s it all about the RIGHT function. Here I have tried to give a summary of this function and its different applications. I have shown multiple methods with their respective examples but there can be many other iterations depending on numerous situations. If you have any inquiries or feedback, please let us know in the comment section.