Function Finds the nth Occurrence of Substrings

 Executive Summary:

Creating a function that finds the nth occurrence of a substring is one of those things I said I’d do someday but had never found the time to do. Because necessity is the mother of invention, "someday" actually arrived when I was recently confronted with a text file full of pathnames that pointed to files I needed to copy. I had to copy the files from a folder on one server to a folder with the same name on another server, then delete the original folder and its contents.

Writing code that copied the files and deleted the original folder was no big deal. However, extracting the folder names from the pathnames proved a little tricky. For example, suppose you need to extract folder names from pathnames that follow the format \RemoteComputerShareNameFolderName. If you know the starting position of the fourth backslash (), you can use VBScript's Right function to extract the folder name. The problem is that VBScript doesn't provide a function that lets you find the fourth occurrence of a substring. Its InStr and InStrRev functions only find the position of a substring's first occurrence. Thus, I created the nInStr function, which lets you find the starting position of any occurrence of a substring within another string.

The nInStr function's syntax is

nInStr(SourceString, SearchForString,WhichOccurrence)

where SourceString is the string being searched, SearchForString is the substring you're looking for, and WhichOccurrence is a number that specifies which occurrence of the substring you want (e.g., 1 for the first occurrence, 2 for the second occurrence, and so on). For example, if you want to know the starting position of the fourth backslash in \DenverSrvrShare1Docs, you'd use

nInStr("\DenverSrvrShare1Docs", _"",4)

If you want to use the nInStr function to help extract a folder's name from a pathname, the call to the function might look something like

SourceString = _ "\DenverSrvrShare1Docs"ExtractFolderName = Right( _ SourceString,Len(SourceString) _ - nInStr(SourceString,"",4))

Listing 1 shows the nInStr function. This function begins with the declaration of its name and three parameters. After that declaration, an initial value of 1 is assigned to a variable named StartPosition. This variable's value indicates where within the source string the function starts its search. A value of 1 means the function will start from the first character in the source string, a value of 2 means the function will start from the second character, and so on. Each time the function cycles through the For…Next statement at callout A in Listing 1, the StartPosition variable's value changes as long as the substring is found.

The key component in the For…Next loop is the code

FoundPosition = InStr(StartPosition, _ strSource,strFind)

When the substring (strFind) is found within the source string (strSource), the starting position of the substring is stored in a variable named FoundPosition. The FoundPosition variable's value is then incremented by 1 and the resulting value is set to StartPosition, thereby creating a new starting position for the next iteration of the For…Next loop. This process continues until the substring isn't found or until the function has looped as many times as the occurrence variable dictates. When the nth occurrence of the substring is found, the starting position of that occurrence is assigned to FoundPosition; that position subsequently becomes the function's return value. When the nth occurrence of the substring isn't found, FoundPosition is set to zero, which becomes the function's return value.

You can download a .zip file that contains the code for the nInStr function by clicking the Download the Code Here button at the top of the page. This .zip file also contains a simple but somewhat entertaining HTML Application (HTA) that demonstrates the function's usage.

Share Your Scripting Experiences

Share your scripting discoveries, comments, solutions to problems, and experiences with products. Email your contributions to [email protected]. Please include your full name and phone number. We edit submissions for style, grammar, and length. If we print your submission, you'll get $100.