How to Find from Right in Excel (6 Methods)

Get FREE Advanced Excel Exercises with Solutions!

While working with long text in Excel you might need to find out a specific character or string in it. You can use the Excel FIND function to find the position of that character or string. But the FIND function will give the position of the character or word from the left of the text. Sometimes you need to find the position from the right or end of the text. You can use the Excel RIGHT function to solve that problem. In this tutorial, I will show you how to use this function in Excel to find from right.

Let’s assume we have an Excel worksheet that contains some text. We will use the Excel RIGHT function to extract a specific string from that text. Along with that, we will also combine this function with the FIND and LEN functions to extract text from a specific character or string. We will also find out the last word of the text using this function with the TRIM, SUBSTITUTE, and REPT functions. Here is a glimpse of the Excel workbook with the text we are going to work with.

Excel Find from Right


1. Using the RIGHT Function to Find a Specific Number of Characters From the Right in Excel

You can use the RIGHT function to extract a specific number of characters from the right of the string or text. You have to do the following.

Steps:

  • We have a string in our cell B5. The cell has a string “The quick brown fox”. We will extract the last 3 characters from the right of this string. That means we will extract the word “fox”.
  • We will write down the following formula in the adjacent D5

=RIGHT(B5,3)

  • The RIGHT function takes exactly two parameters:

          Text -Required. The text string contains the characters you want to extract.

          Num_chars-Optional. Specify the number of characters you want RIGHT to extract.

  • Upon entering the formula, we will get the extracted word “fox” in cell D5.
  • We will do the same for the rest of the texts but with a different number of characters.

RIGHT Function to Extract Specific Number of Characters

Read More: How to Find Character in String from Right in Excel


2. Applying the RIGHT Function to Extract the Last Character of the String

You can also use the RIGHT function to extract the last character of a string or text. Follow the below steps:

Steps:

  • Instead of entering the value for the second argument (Num_chars), we will leave it empty. The formula for only extracting the last character will be like the formula below:
=RIGHT(B5)
  • Upon entering the formula, we will get the extracted character “x” in cell D5.
  • We will do the same for the rest of the texts.

RIGHT Function to Extract Specific Number of Characters

Read More: Excel Find Last Occurrence of Character in String


3. Utilizing the RIGHT Function When Exceeds the Length of the String

The RIGHT function will return the whole text or string if the second argument (Num_chars) exceeds the length of the string. Let’s do the following to find out.

Steps:

  • We will write down the same formula but we will enter a number for the second argument which is larger than the length of the whole text. For this example, we have entered the number 100. So, our formula becomes:
=RIGHT(B5,100)
  • Upon entering the formula, we will get the entire text in cell D5.
  • We will do the same for the rest of the texts.

Excel Find from Right

Read More: How to Find a Character in String in Excel


4. Imposing the RIGHT function on Numeric Values

The RIGHT function will return the same numeric value if we apply it to a number.

Steps:

  • We will write down the same formula but we will apply it to a number.
=RIGHT(B5,100)
  • Upon entering the formula, we will get the entire text in cell D5.
  • We will do the same for the rest of the texts.

RIGHT Function on Numeric Values


5. Extracting Characters from the Right of a Specific Character

The RIGHT function is often combined with other functions like LEN and FIND to extract text in more complex formulas. For example, to extract text in cell B5 to the right of a specific character (), use RIGHT with the FIND and LEN functions like this:

Steps:

  • We will write down the formula below in cell B5:
=RIGHT(B5,LEN(B5)-FIND("y",B5))
  • In this formula, the RIGHT function has LEN(B5)-FIND(“y”,B5) as the second argument (Num_chars). LEN(B5) calculates the total number of characters in the string which is 22 and FIND(“y”, B5) calculates the position of the character “y” in that string and that is 7. When we subtract LEN(B5) from FIND(“y”, B5), we will get the number of characters right of the character “y”. So, we will get 15.  
  • Now, the RIGHT function has B5 or the text as the first argument and the 15 as the second argument. So, it will extract the 15 characters from the end or right of the string. So, we will get all the characters right of the character “y”.
  • We will do it for the other string but we will select different characters each time.

RIGHT Function to Extract Characters from the Right of a Specific Character


6. Finding the Last Word From the Right in Excel

We can use a formula that is a combination of the TRIM, SUBSTITUTE, RIGHT, and REPT functions to extract the last word from a text string. In the example shown, the formula in B5 is:

Steps:

  • We will write down the formula below in cell B5.
=TRIM(RIGHT(SUBSTITUTE(B5," ",REPT(" ",100)),100))
  • This formula is an interesting example of a “brute force” approach that is based on the fact that TRIM will strip all the leading spaces irrespective of how many we have in that text.
  • Now, we will use the same formula for the other string.

Extract Last word from right

Read More: How to Find Text in Cell in Excel


Things to Remember

  • If we do not insert any number as the second argument for the RIGHT function, it will just extract the last character of the string.
  • If the second argument exceeds the total length or character of the string, then it will return the whole string or text.

Download Practice Workbook

Download this practice book to exercise the task while you are reading this article.


Conclusion

In this article, we have learned to use Excel to find from right. If you have any queries or recommendations about this article, please do leave a comment below. Have a great day!!!


Related Articles


<< Go Back to Find in String | String Manipulation | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
ASM Arman
ASM Arman

Abu Saleh Arman is a Marine engineer and Excel & VBA expert. He loves programming with VBA. He finds VBA programming a time-saving tool to manipulate data, handle files, and interact with the internet. He is very interested in Python, MATLAB, PHP, Deep Neural Networks, and Machine Learning, showcasing his diverse skill set. Arman holds a B.Sc in Naval Architecture & Marine Engineering from BUET, Bangladesh. However, he switched to a content developer, where he writes technical content... Read Full Bio

2 Comments
  1. Thank you for the great information, althought I’m yet to digest the marvelous concept of brute-forcing the last “word” from the string, the screen shot example for Method 6 is misplaced (shown with formulas from method 5).

    • Dear Xyand,

      You are most welcome. Thanks for appreciation. We updated the image of Method 6.

      Regards
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo