How to Find Character in String from Right in Excel (4 Easy Methods)

The article will describe the procedure to find character(s) in a string from right in Excel. Sometimes we need to store the last data of a character string in Excel. Suppose we want to store the surnames of a certain group of people in a column. In that case, we need to extract the characters in a name from the right.

Here, I’ll be using the following dataset to describe the tactics to find characters in a string from its right side. Suppose a group of people work in an office and they have their own ID and User ID. We are going to work on their Names, ID and User ID in regard to explain our problem and its solution.

excel find character in string from right


Download Practice Workbook


4 Ways to Find Character in String from Right in Excel

1. Using Excel RIGHT Function to Find Character in String from Right

The simplest way to find the characters in a string from right is to use the RIGHT function. Suppose we want to store the numbers in the ID in a column. Let’s discuss the strategy below.

Steps:

  • First, make a new column and type the following formula in cell E5.
=RIGHT(C5,3)

excel find character in string from right

Here, the RIGHT function takes the character string in cell C5 and finds the last 3 characters from it. As each ID has 3 numbers, we put [num_chars] as 3.

  • Hit the ENTER button and you will see the last 3 digits of the ID in cell C5.

excel find character in string from right

This operation will provide you with the numbers in the ID in column E. Thus you can find the characters in a string from the right and store them in a cell.

Read More: How to Find Character in String Excel (8 Easy Ways)


2. Applying Excel LEN and FIND Functions to Extract Character in String from Right

Suppose we want to extract the surnames from these people’s names. We can follow the tricks below.

Steps:

  • Make a new column for surnames and type the following formula in cell E5.
=RIGHT(B5,LEN(B5)-FIND(" ",B5))

excel find character in string from right

Here, we identify the position of the Space between first name and surname with the help of the FIND function and then subtract this position from the length of the string in cell B5 (whole name). This way, we tell the RIGHT function which characters it should store in cell E5. We used the LEN function to determine the length of the string of cell B5.

  • Now press the ENTER button and you will see the surname of cell B5 in cell E5.

excel find character in string from right

  • Use the Fill Handle to AutoFill lower cells.

After that, you will see the surnames in column E. This is another method you can apply to find the characters in a string from the right and store them in a cell.

Read More: How to Find from Right in Excel (6 Methods)


Similar Readings


3. Utilizing Combined Functions to Find Character in String from Right

Imagine you just want to store the number from the User ID of these guys. We can do this by nesting the LEN, FIND and SUBSTITUTE functions into the RIGHT function. Let’s see how we can fulfill our purpose.

Steps:

  • Make a new column for User ID No and type the following formula in cell E5.
=RIGHT(D5,LEN(D5)-FIND("#",SUBSTITUTE(D5,"-","#",LEN(D5)-LEN(SUBSTITUTE(D5,"-","")))))

excel find character in string from right

Here, we have nested LEN, FIND and SUBSTITUTE in the RIGHT function to extract the UID No as text. Let’s break the formula down into pieces below.

Formula Breakdown

  • LEN(D5)—-> The Length function returns the number of characters.
    • Output: 11
  • SUBSTITUTE(D5,”-“,””)—-> The SUBSTITUTE  function replaces the hyphens with nothing.
  • SUBSTITUTE(“PLK-OIQ-249″,”-“,””)—-> becomes PLKOIQ249
    • Output: “PLKOIQ249”
  • LEN(SUBSTITUTE(D5,”-“,””))—-> becomes LEN(“PLKOIQ249”)
    • Output: 9
  • LEN(D5)-LEN(SUBSTITUTE(D5,”-“,””)))—-> becomes LEN(D5)-LEN(“PLKOIQ249”)
    • 11-9
    • Output : 2
  • SUBSTITUTE(D5,”-“,”#”,LEN(D5)-LEN(SUBSTITUTE(D5,”-“,””))—-> becomes
  • SUBSTITUTE(D5,”-“,”#”,2)—>  It substitutes the 2nd hyphen ‘-’ with hashtag ‘#’)
    • Output: “PLK-OIQ#249” 
  • FIND(“#”,SUBSTITUTE(D5,”-“,”#”,LEN(D5)-LEN(SUBSTITUTE(D5,”-“,””))))—-> becomes
  •  FIND(“#”,”PLK-OIQ#249″)—-> The FIND function finds the position of the given character #.
    • Output: 8
  • RIGHT(D5,LEN(D5)-FIND(“#”,SUBSTITUTE(D5,”-“,”#”,LEN(D5)-LEN(SUBSTITUTE(D5,”-“,””)))))—-> turns into
  • RIGHT(D5,LEN(D5)-8)—-> 
  • RIGHT(D5,11-8)—-> 
  • RIGHT(D5,3)—->
  • RIGHT(“PLK-OIQ-249”,3)—-> The RIGHT function extracts the number of characters from the right side.
    • Output: 249

Finally, we get the User ID 249. Let’s move into the steps again.

  • Hit ENTER and you will only see the number in the User ID.

  • After that, use the Fill Handle to AutoFill lower cells.

excel find character in string from right

Thus you can accommodate the numbers in the User ID in column E. It is a little bit difficult to find characters in a string from its right side when this type of situation arises.

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


4. Finding Characters in String from Right Using Flash Fill

If you are not a formula guy, you can use the Flash Fill command to find characters in a string from its right. Say you want to store the surnames of these people. Let’s discuss this simple process.

Steps:

  • Make a new column for surnames and type the surname (Spears) in cell B5.
  • Select Home >> Fill >> Flash Fill

Here the Flash Fill command follows a pattern. It detects the character string Spears as the right-sided characters of the whole string in cell B5. And so it will do the same for other cells.

  • This operation will return all the surnames in the remaining cells E6 to E11.

excel find character in string from right

Thus you can find characters in a string from its right position.

Read More: Excel Function: FIND vs SEARCH (A Comparative Analysis)


Practice Section

Here I’m giving you the dataset that we used to explain how to find characters in a string from its right position so that you can practice on your own.


Conclusion

The article provided some methods on how to find characters in a string from its right. The methods that I described here are pretty easy to understand. I hope this will be fruitful for you to solve your own problems in this regard. If you have more efficient methods or ideas or any feedback, please leave them in the comment box. This might help me to enrich my upcoming articles.


Related Articles

Nahian

Nahian

Hello, Nahian here! I do enjoy my efforts to help you understand some little basics on Microsoft Excel I've completed my graduation in Electrical & Electronic Engineering from BUET and I want to be a successful engineer in my life through intellect and hard-work, and that is the goal of my career.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo