When you need to find the position of a character inside a string or the occurrence of a substring inside a string, the VBA InStrRev function might be the best option for you. Unlike the InStr function, the InStrRev function starts searching from the end of the string. In this article, I’ll discuss the ins and outs of the InStrRev function including 7 examples also with the necessary explanation.
Firstly, let’s have a look at the overview of the InStrRev function.
Right away, I’ll show you the uses of the function elaborately. Before that, let’s be introduced to the function.
Download Practice Workbook
Introduction to the VBA InStrRev Function in Excel
InStrRev, which refers to “In String Reverse”, is a VBA function to get the position of a substring within a given string. If you enter the function in the module, you’ll see such as the following screenshot.
- Function Objective:
Finds out the position of a string that occurs within another, starting from the beginning.
The InStrRev function has four arguments. These are the following-
|StringCheck||Required||The string from where you want to get the position|
|StringMatch||Optional||The substring that you wish to get|
|Start||Optional||Refers to starting position. The default value of this argument is -1 (starting from right to left); if you omit the value.|
|Compare||Optional||Represents three kinds of comparisons as shown in the following table. Excel takes vbBinaryCompare (value:0) if you skip the value.|
Three types of comparisons of the Compare argument are-
|vbUseCompareOption||-1||Uses option compare|
- Return Value:
Returns a numeric value.
Examples of Using VBA InStrRev Function in Excel
After finishing the introduction part, you have concrete knowledge about the function. So, we may accelerate the knowledge with proper examples. In the first 6 examples, you’ll see the uses of the InStrRev function for a single string i.e. “Miles to Go Before I Sleep”. In the last example, I’ll discuss the use of the function in the case of a dataset or worksheet if you need it.
Let’s dive into the examples.
1. VBA InStrRev to Find out the Position of a Substring
In the beginning, we’ll see the uses of the function to find out the position of a substring “M” inside the string “Miles to Go Before I Sleep” in step by step process.
Firstly, open a module by clicking Developer > Visual Basic.
Secondly, go to Insert>Module.
Then, copy the following code in your module.
Sub Finding_The_Position_Substring() Dim A As Integer A = InStrRev("Miles to Go Before I Sleep", "M") MsgBox A End Sub
Finally, run the code (F5 is the keyboard shortcut).
Then, you’ll see the output is 1.
Note: Sometimes the StringCheck is called simply a “string” and the StringMatch is also called a “substring”.
2. VBA InStrRev to Get the Position of a Substring with Given Starting Position
In the previous example, we skip the value of the Start argument. What if we put 10 as a starting position for the substring “e”?.
For doing this, you have to insert a module as shown earlier and copy the following code into the module.
Sub Getting_Position_Substring_Given_Starting_Position() Dim A As Integer A = InStrRev("Miles to Go Before I Sleep", "e", 10) MsgBox A End Sub
If you run the code, you’ll get the output is 4.
Related Content: How to Use Fix Function in Excel VBA (4 Examples)
3. VBA InStrRev to Extract the Position in Case Sensitivity
At the current example, we’ll examine whether the InStrRev function is case sensitive or not
That means, if we put the substring as “g” and run the code, what will be the output.
After running the code, if we get 0 representing that the function is not case sensitive.
Copy the following code into a new module.
Sub Extracting_The_Position_Case_Sensitivity() Dim A As Integer A = InStrRev("Miles to Go Before I Sleep", "g") MsgBox A End Sub
If you run the code, the output will look as follows.
As the output is 0, it is clear that the InStrRev function is not case-sensitive.
But we left another important issue.
In the above code, we skip the value of the Compare argument, so Excel takes vbBinaryCompare as the default value.
Interestingly, if you input the argument as vbTextCompare, you’ll see a different output for the same string and substring.
So the adjusted code will be the following.
Sub Extracting_The_Position_Case_Sensitivity() Dim A As Integer A = InStrRev("Miles to Go Before I Sleep", "g", , vbTextCompare) MsgBox A End Sub
After running the code, you’ll get the output is 10 (not 0), that is to say, the function is case sensitive in this case.
To sum up, we can say that the case sensitivity of the InStrRev function depends on using the Compare argument
- VBA Date Function (12 Uses of Macros with Examples)
- How to Unhide Top Rows in Excel (7 Methods)
- Excel Date Picker for Entire Column
- Excel Formula to Generate Random Number (5 examples)
- How to Use VBA ChDir Function in Excel (4 Suitable Examples)
4. VBA InStrRev to Retrieve the Position of Last Space
Again, you may retrieve the position of the last space (“ “) from a given string.
Copy the following code and insert that in a new module.
Sub Retrieve_Position_Last_Space() Dim A As Integer A = InStrRev("Miles to Go Before I Sleep", " ") MsgBox A End Sub
If you run the code, you’ll find the following output.
Related Content: How to Use VBA Replace Function in Excel (11 Applications)
5. VBA InStrRev to Get the Position of Second to Last Space
More importantly, if you wish to find the position of second to the last space, you may use the InStrRev function.
In such a situation, you have to utilize the function two times.
For the first time, the InStrRev function returns the position of the last space.
And the second time, the InStrRev function returns the position of second to the last space.
Copy the following code into a new module.
Sub Getting_Position_Second_to_Last_Space() Dim LastPos As Integer Dim SecondLastPos As Integer LastPos = InStrRev("Miles to Go Before I Sleep", " ") SecondLastPos = InStrRev("Miles to Go Before I Sleep", " ", LastPos - 1) MsgBox SecondLastPos End Sub
Next, run the code and the output will look as follows.
6. VBA InStrRev to Find out the Position of “ZZ” within the String
In the last example of a single string, we’ll see the process of finding out the position of “ZZ” within the string.
Here the “ZZ” means such a substring (StringMatch) that is not available in the string.
So copy the code and insert it into a new module.
Sub Finding_The_Position_ZZ() Dim A As Integer A = InStrRev("Miles to Go Before I Sleep", "ZZ") MsgBox A End Sub
Finally, run the code and the output is 0 as shown in the following figure.
7. VBA InStrRev to Retrieve the Position of Last Space in Case of a Dataset
In maximum cases, we use a dataset in Excel and want to find the output for the dataset.
For your convenience, I’ll show the use of the InStrRev function in the case of a dataset.
Assuming that in our dataset, we have four strings (starting from B5 cell) and we want to find the position of the last space for each string.
Next, copy the following code after creating a new module.
Sub Dataset_Example_InStrRev() Range("D5").Value = InStrRev(Range("B5"), " ") Range("D6").Value = InStrRev(Range("B6"), " ") Range("D7").Value = InStrRev(Range("B7"), " ") Range("D8").Value = InStrRev(Range("B8"), " ") End Sub
Subsequently, run the code and the output will be as follows.
Related Content: How to Use LCase Function in VBA in Excel (With 4 Examples)
Things to Remember
1. The InStrRev function finds 0 if the string match or substring is not found.
2. The returns Null if the string check or string match is Null.
3. If the value of the Start argument is larger than the length of the string check, the function returns 0.
4. The InStrRev and InStr functions are not the same in VBA.
In short, I discussed the basics of the VBA InStrRev function at first. Then 7 distinct examples are also shown for accelerating the knowledge. Anyway, if you have any queries and suggestions, please don’t forget to share them in the following comments section.