Introduction | Example | Tutorial | Applications
Introduction - VBA InStrRev
A couple weeks ago, I published an article about the VBA InStr function and described how incredibly useful it is. Today, let’s discuss another pretty useful string manipulation functions - InStrRev.
InStrRev is a close relative of the Instr function. Instead of searching for a substring from left to right, however, InStrRev searches from right to left. If a match is found, it will return the position of the substring inside the parent string. In other words, InStrRev will find the last instance of a substring inside a string, whereas InStr is used to find the first instance of a substring inside a string.
Because it reads from right to left, InStrRev is the perfect function for extracting file paths, URLs, or directories from a text string.
Example - VBA InStrRev
Extract File Path
Suppose you have a list of links to documents, pictures, applications or any other file on your computer. You’re not interested in opening the file - you just want to extract the folder where the file is stored. InStrRev makes solving this problem simple.
Sub ExtractFilePath()
Dim strFile As String, strPath
Dim iEnd As Long
strFile = Range("A1") 'store file name
iEnd = InStrRev(strFile, "\") 'This is where your folder path ends
strPath = Mid(strFile, 1, iEnd)
End SubIn this example, pretend the value in cell “A1” is
If you want to remove the trailing backslash, change
strPath = Mid(strFile, 1, iEnd)to
strPath = Mid(strFile, 1, iEnd-1)Make powerful macros with our free VBA Developer Kit This is actually pretty neat. If you have trouble understanding or remembering it, our free VBA Developer Kit can help. It’s loaded with VBA shortcuts to help you make your own macros like this one - we’ll send a copy, along with our Big Book of Excel VBA Macros, to your email address below.
You can apply the same principle to a number of scenarios.
Extract URL
Let’s say you have a long string and somewhere in that string is a website url. It could be in a text file or a body of an email - who knows. You don’t know where the link is or how long the link is, but you want to extract the domain. You can use InStrRev to help you solve this problem, too.
Sub ExtractWebsite()
Dim strString As String, strURL
Dim iEnd As Long, iStart As Long
strString = "Visit my website at http://wellsr.com for more information." 'The string you want to search
iStart = InStrRev(strString, "http")
iEnd = InStrRev(strString, ".com") 'This is where your url ends
strURL = Mid(strString, iStart, (iEnd - iStart) + 4)
End SubAt the end of this example,
You can modify the macro to support “.org” domain extensions or to search on “www.” To find subpages on a website instead of just the domain, you can make your InStrRev function search for “/” instead of “.com”. This macro is highly flexible and there are many ways to modify it to solve a number of problems.
Tutorial - VBA InStrRev
InStrRev Arguments
The InStrRev VBA function accepts 4 arguments, 2 of which are optional:
InStrRev(MainString, SubString, [Start], [Compare As VbCompareMethod])Oddly, the arguments are required in a different order than the arguments of the InStr function. Anyway, here’s a description of the arguments:
| MainString | The string you want to search within. You want to search for SubString inside of MainString. |
| SubString | The substring you want to search for. You want to search for SubString inside of MainString |
| Start | [Optional] This is the positive integer position where you want your string to begin searching for SubString inside MainString (Remember, InStrRev searches right to left!). If omitted, it starts from the end of your string. A value less than or equal to 0 yields an error and a value greater than the length of your string returns a 0. |
| Compare | [Optional] This tells VBA which algorithm to use when comparing. There are three possible choices, described below. |
Here are the three possible VbCompareMethods for the Compare argument:
- vbBinaryCompare (Default)
- vbDatabaseCompare
- vbTextCompare
The default, vbBinaryCompare, is adequate for the vast majority of applications, so I’m not going to waste time explaining each algorithm. Leave a comment in the comments section if you want me to dive into more detail.
InStrRev Fundamentals
Now that we got the arguments out of the way, let’s talk fundamentals.
InStrRev returns an Integer - actually a Variant of type Long - showing where the substring occurs in the main string. Although InStrRev starts from the right side of the string and moves left, the integer location it returns is the position of the start of the string as if you were counting left to right.
For example,
i = InStrRev("1231234", "23")returns a value of “5” because the first instance of “23” starting from the right begins at the 5th character in the string.
If the substring does not exist, InStrRev returns a value of 0, just like the InStr function did.
The InStrRev vbBinaryCompare is case-sensitive. If you want to search for a substring inside a main string and you’re not worried about case sensitivity, it’s a good practice to wrap your MainString and SubString arguments in the LCase function. You could use the vbTextCompare algorithm, but it’s a little buggy. It’s especially buggy when handling special characters.
If InStrRev(LCase(MainString), LCase(SubString)) = 5 ThenApplication Ideas
Admittedly, InStrRev isn’t as useful as my favorite string manipulation function - InStr. Even so, it can be used for more than just extracting URLs and folder paths. You can also use it to extract email addresses!
Basically, anything you can do with InStr, you can do with InStrRev, although it might not be the best choice. You can use it to do advanced filtering and data extracting, especially if you know your strings contain multiple instances of the word you want to find and you’re only interested in the last instance. InStrRev is a great choice for this scenario.
That’s all for this tutorial. When you’re ready to take your VBA to the next level, subscribe using the form below.
