Extract URLs and Folder Paths with VBA InStrRev

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 Sub

In this example, pretend the value in cell “A1” is C:\Users\Ryan\Documents\File.txt. Once you run this macro, the file path is extracted and stored in the variable “strPath” by using the Mid function. The result? C:\Users\Ryan\Documents\.

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 Sub

At the end of this example, strURL contains the link http://wellsr.com.

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:

Here are the three possible VbCompareMethods for the Compare argument:

1. vbBinaryCompare (Default)
2. vbDatabaseCompare
3. 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 Then

Application 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.