This tutorial talks about how to reverse search in Excel. There are two different methods to do this that I will explain in this tutorial. The methods use different formulas to do the same. One of these methods uses VBA code, while the other method makes use of the built-in Excel functions. Both the methods make it pretty easy to do a reverse string search in Excel.
Reverse searching can be a useful technique in case you want to find the position of a certain character or word from the end of a string. As Excel does not have any native formula to do that, so you will have to do it by yourself. Using the methods explained below, you can easily perform searches from the end of a string in Excel.
Searching in MS Excel files is nothing new, there are already some add-ins to search and find in Excel sheets. But the search offered by them is a straight search; it cannot search in the reverse direction. That’s why I have listed the following methods to perform a reverse search in Excel.
How to Perform Reverse Search in Excel?
As I said above, there are two different methods that I will use to perform a reverse search in Excel. In the first method I will use a user defined function that is written in VBA. And I will use some built-in functions of Excel in the second formula to do the same. Let’s have a look at them one by one.
Method 1: Reverse String Search in Excel Using VBA:
In this method I will show you how to perform reverse searches in Excel to find the position a certain character or a word from the end of a string. In this formula I will use a user defined function ReverseSearch, written in VBA whose syntax and code is given below. This formula receives two parameters, one for the source string and second for the word that is to be searched. After that it returns the position of the first character of the specified word from the end of the string.
ReverseSearch(Text_String, Word_to_Find)
See its VBA code here:
Public Function ReverseSearch(strBase As String, strTerm As String) As Integer
'Purpose: Returns the position of string from the end
Dim x As Integer
On Error GoTo ErrorMessage
If strTerm = "" Then
ReverseSearch = 0
Else
x = Len(strTerm)
ReverseSearch = InStr(StrReverse(strBase), StrReverse(strTerm))
ReverseSearch = (ReverseSearch + x) - 1
End If
Exit Function
ErrorMessage:
MsgBox "There seems to be an error."
End
End Function
You have to put the above code in VBA module of Excel. For that, click Alt+F11. In the window that opens up, right click on name of your Excel, click on Insert > Module. In the window that opens up, paste the entire VBA code I have given above. Then click on Save, and save the Excel as “Excel Macro-Enabled Workbook .xlsm” file. Then close the macro window.
Now, you can use the formula (that I highlighted in Red above) to do reverse search in Excel. You can see the below screenshot, showing the working of this formula.
In the above screenshot I have used this formula to find the position of a certain character from the end of the string. And also, I have used it to find the position of a specified word. You can see that it has resulted in the correct position of the specified word and character in both the cases.
This is a very simple method to do reverse search in Excel, as you just need to paste the VBA code, and then you can do as many reverse searches as you want. Just paste the VBA code once in the Excel macros and then you can use it anytime unless, you remove Excel from your PC.
Method 2: Reverse String Search in Excel Using Excel Functions:
In the above method I explained how to reverse search using VBA. Now, if you don’t want to use VBA functions or you find it difficult to use them in Excel, then there is another function that can do it for you. Using the built-in functions of Excel I have made another formula that can do reverse search in Excel. Though it’s a little bit tricky, but once you understand its functionality, then I am sure that you will like it. The syntax of this formula is given below.
LEN(Text_String) – SEARCH(“^^^”, SUBSTITUTE(Text_String, Word_to_Find, “^^^”, (LEN(Text_String)-LEN(SUBSTITUTE(Text_String,Word_to_Find, “”)))/LEN(Word_to_Find))) + 1
This formula uses Search, Substitute, and Len functions of Excel to do Reverse string search.
The way this formula works is that it finds the last instance of the specified substring and then replaces it with “^^^”, and returns its index. And after that, obtained index is subtracted from the length of the source string to get its position from the end. However, if your source string contains the sequence “^^^”, then this formula will fail and you will need to replace “^^^” in above formula to some other string (that doesn’t exist in your text).
After using this formula, you can easily find the position of a word from its first occurrence in the source string from the end. You can see the working of the formula in the following screenshot.
Now, after seeing this screenshot, you will get the idea how this formula works to find the position of certain word using the reverse search technique in Excel. Do note that this formula is made by using the built-in excel functions. So, if you don’t want to use an external formula written in VBA, then you may give this a try.
In this way you can perform a reverse search in Excel using this simple formula. Based on your needs, you can search a particular character or a word in your source string in your worksheet.
Also see: How to do Reverse vLookup in Excel.
My Final Verdict
There have been a lot of times when I had to do reverse string search in Excel. The first time I had to do I was very surprised to know that Excel does not provide any functionality to do reverse string search in Excel. And when I started looking for a solution, I wasn’t able to find any easy one.
Now, I have provided 2 solutions to you to do reverse string search in Excel. Both work equally well, and both give correct results. I personally prefer the VBA code, because it is simple to use. But if you want to stick to core Excel functions, then the second method works equally well.
In case you know some other ways to do reverse string search in Excel, do let me know in comments below.
