How to Use RIGHT Function in Excel (With 6 Easy Examples)

Overview of RIGHT function

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)

Overview of RIGHT function

Excel RIGHT Function: Syntax & Arguments

Excel RIGHT Function Syntax & Arguments

Summary

Syntax

=RIGHT (text, [num_chars])

Arguments

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.

Note: 

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

How to Get a Substring that Comes after a Certain Character

Step 1: Enter the formula in cell C4

=RIGHT(B4,LEN(B4)-SEARCH(" ",B4))

Formula Explanation

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

Enter the formula using RIGHT LEN SEARCH function

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.

How to Extract a Substring after the Last Occurrence of the Delimiter

Step 1: Enter the formula in cell D4 and copy up to D11

=RIGHT(C4,LEN(C4)-SEARCH("$",SUBSTITUTE(C4,":","$",LEN(C4)-LEN(SUBSTITUTE(C4,":","")))))

Formula Explanation

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

Enter formula using RIGHT LEN SEARCH SUBSTITUE functions

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

=RIGHT(C4, LEN(C4)-10)

Formula Explanation

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

Enter formula using RIGHT and LEN function

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.

Extract number from string using RIGHT function in Excel

Step 1: Enter the formula in cell F4 and copy it down up to F11

=VALUE(RIGHT(E4, 5))

Formula Explanation

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

 Formula using VALUE and RIGHT Function

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.

Extract Domain Name from Email using RIGHT Function

Step 1: Enter the formula in F4 and copy it down up to F11

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

Formula Explanation

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

enter formula using LEN RIGHT Function

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.

Modify URL Using RIGHT Function

Step 1: Enter the formula in cell C4 and copy it down up to C8

=LEFT(B4,LEN(B4)-(RIGHT(B4)="/"))

Formula Explanation

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

Enter formula using LEN LEFT and RIGHT function

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.

Conclusion

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.


Further Readings:

Md. Abdullah Al Murad

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo