VBA INSTRREV

What is Excel VBA INSTRREV?

VBA INSTRREV is a built-in string function used to determine the position of one string within another, starting the search from the end of the string rather than the beginning. In simpler terms, it helps you find a substring’s last occurrence or any specific occurrence from the end in a given string.

In the example below, we aim to extract the file extension from a given filename. We start by defining a subroutine named ExtractFileExtension with two string variables, filename and fileExtension. The filename is assigned the value “document_report.pdf.” To extract the file extension, the VBA INSTRREV function is used to find the position of the last period (.) in the filename. The Mid function in VBA retrieves the substring starting after this position.

Finally, a message box is displayed to show the extracted file extension, which, in this case, is “pdf”.

Syntax

The syntax of INSTRREV is as follows:

INSTRREV(string_check, string_search[, start[, compare]])

Parameter	Description
string_check	The string you wish to search in.
string_search	The substring you want to find.
start (optional)	The starting position for the search, counting from the end. Default is -1 (the end of the string).
compare (optional)	The type of comparison to perform.

• Return Values based on the string_check Value:

InStrRev returns the following values.

If the string you search in is	InStrRev returns
– of length zero	0
– Null	Null
– zero-length	start
– Null	Null
– not found	0
– start > Len(stringcheck)	0

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

How to Use VBA INSTRREV Function?

Examples

Example #1 – Finding the Last Occurrence of a Character

In this example, we will find the last occurrence of the letter “l” in the string “Hello World” and displays its position in a message box.

• Step 1: In the new module, we will first define a new subroutine named LastOccurrence.

• Step 2: Here, we will be declaring a variable named result of type Integer. This variable will store the position of the last occurrence of our desired character.

• Step 3: We use the InStrRev function to find the last occurrence of the character “l” in the string “Hello World.” The result is stored in the result variable.

• Step 4: Here, we display a message box that shows the position of the last occurrence of “l.”

• Step 5: Now, save the macro and click on run. When executed, the VBA InStrRev function starts counting from the first character (position 1), and the last “l” in “Hello World” is the tenth character in the string.

The VBA code displays a message box stating that the last occurrence of the letter “l” in “Hello World” is at position 10.

Here is the full code:

Sub LastOccurrence()
Dim result As Integer
result = InStrRev(“Hello World”, “l”)
MsgBox “The last occurrence of ‘l’ is at position: ” & result
End Sub

Example #2 – Finding the Second Last Occurrence of a Character

In this example, we identify the second to last occurrence of the letter “l” in the string “Hello World” and display its position in a message box using the VBA Instrrev second occurrence function.

• Step 1: First, we initiate a subroutine named SecondLastOccurrence.

• Step 2: In this step, two integer variables, positionFirst and positionSecond, are declared. They will store the positions of the last and second last occurrences of “l”, respectively.

• Step 3: Next, we find the last occurrence of “l” in “Hello World” and store its position in positionFirst.

• Step 4: To find the second last occurrence, we start the search from one position before the last occurrence. This ensures that InStrRev doesn’t return the same occurrence again.

• Step 5: Here, a message box displays the position of the second last occurrence.

• Step 6: Now, when you save and click on Run, InStrRev first identifies the last “l” at position 10.

Then, by using the position of this last “l” minus one as the starting point, the function searches again in reverse to find the next “l”, which is the second last occurrence, located at position 4.

A message box appears indicating that the second to last occurrence of the letter “l” is at position 4.

Here is the full code:

Sub SecondLastOccurrence()
Dim positionFirst, positionSecond As Integer
positionFirst = InStrRev(“Hello World”, “l”)
positionSecond = InStrRev(“Hello World”, “l”, positionFirst – 1)
MsgBox “The second last occurrence of ‘l’ is at position: ” & positionSecond
End Sub

Example #3 – Extracting Domain from Email

In this example, we will extract and display the domain part of the email “john.doe@example.com” using the VBA InStrRev and Mid functions.

• Step 1: We start by defining a new subroutine named ExtractDomain.

• Step 2: Next, two string variables, email and domain, are declared. ‘email’ stores the email address, while ‘domain’ stores the extracted domain.

• Step 3: We assign the email address “john.doe@example.com” to the email variable.

• Step 4: To extract the domain, we must find the position of “@” and extract everything after it. We use InStrRev to find the position of “@” and Mid function to extract the substring from one position after “@” until the end of the string.

• Step 5: Finally, a message box displays the extracted domain from the email address.

• Step 6: Here the macro uses the InStrRev function to find the position of the “@” symbol in the email string. The Mid function then extracts the domain portion from the position right after the “@” symbol to the end of the string.

Upon saving the macro and clicking on “Run,” a message box appears indicating that the domain of the email is “example.com.”

Here is the full code:

Sub ExtractDomain()
Dim email, domain As String
email = “john.doe@example.com”
domain = Mid(email, InStrRev(email, “@”) + 1)
MsgBox “The domain of the email is: ” & domain
End Sub

Important Things to Note

• VBA InStrRev searches for a substring from the end of a string, while VBA INStr starts its search from the beginning.
• The function will return 0 if the substring is not found. If the substring or the string itself is of zero length, it will return the value of the start argument.
• While InStrRev is optimized for searching strings in reverse, using this function repetitively on very long strings may have performance implications.
• The term “VBA InStrRev Beispiel,” which translates to “VBA InStrRev Example,” means demonstrating the usage of the VBA InStrRev function to search for a substring within a main string from the end.
• In Outlook VBA InStrRev scenarios, the function is particularly useful for parsing and extracting specific data from email content, sender addresses, or subjects.

Frequently Asked Questions (FAQs)

Recommended Articles

This has been a guide to VBA InStrRev. Here we learn how to use the InStrRev function in Excel VBA coding, its syntax, along with step-by-step examples. You can learn more from the following articles –

• VBA FILEDIALOG
• VBA Substring
• VBA GetOpenFileName