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.
How to Find Character in String from Right in Excel: 4 Ways
1. Using Excel RIGHT Function to Find Character in String from Right
The simplest way to find the characters in a string from the 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)
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.
- Now use the Fill Handle to AutoFill lower cells.
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
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))
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.
- 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
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,"-","")))))
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.
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 Text in Cell 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.
Thus you can find characters in a string from its right position.
Read More: How to Check If Cell Contains Specific Text in Excel
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.
Download Practice Workbook
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.
