MS-Excel / Functions and Formula

---

Using InStr and InStrRev to Find a String within Another String

The InStr function is designed to find one string within another string. For example, you could check, say, the current paragraph to see if it contained a particular word. If it did, you could take action accordingly - for instance, replacing that word with another word or selecting the paragraph for inclusion in another document.

The InStrRev function is the counterpart of the InStr function, working in a similar way but in the reverse direction.

The syntax for InStr is as follows:

InStr([start, ]string1, string2[, compare])

Here are the arguments:

• start is an optional argument specifying the starting position in the first string, string1. If you omit start, VBA starts the search at the first character in string1 (which is usually where you want to start). However, you do need to use start when you use the compare argument to specify the type of string comparison to perform.
• string1 is a required argument specifying the string expression in which to search for string2.
• string2 is a required argument specifying the string expression for which to search in string1.
• compare is an optional argument specifying the type of string comparison you want to perform. Text can be compared two ways: a binary comparison, which is case sensitive, or a textual comparison, which is not case sensitive. The default is a binary comparison, which you can specify by using the constant vbBinaryCompare or the value 0 for compare. Although specifying this value isn't necessary (because it's the default), you might want to include it to make your code ultra-clear. To specify a textual, case-insensitive comparison, use the constant vbTextCompare or the value 1 for compare.

Another way to use InStr is to find the location of a certain string within another string so that you can then change that substring. You might want to do this if you needed to move a file from its current position in a particular folder or subfolder to another folder that had a similar subfolder structure. For instance, suppose you work with documents stored in a variety of subfolders beneath a folder named In (such as z:\Documents\In\), and after you're finished with them, you save them in corresponding subfolders beneath a folder named Out (z:\Documents\Out\). The short procedure shown in Listing-1 automatically saves the documents in the Out subfolder.

Listing-1: Changing a file path

1. Sub Save_in_Out_Folder()
2.     Dim strOName As String, strNName As String, _
	   intToChange As Integer
3.     strOName = ActiveDocument.FullName
4.     intToChange = InStr(strOName, "\In\")
5.     strNName = Left(strOName, intToChange - 1) & "\Out\" _
	   & Right(strOName, Len(strOName) - intToChange - 3)
6.     ActiveDocument.SaveAs strNName
7. End Sub

The code in Listing-1 works as follows:

• Line 1 begins the procedure, and line 7 ends it.
• Line 2 declares the String variable strOName (as in original name), the String variable strNName (as in new name), and the Integer variable intToChange. Line 3 then assigns strOName the FullName property of the ActiveDocument object: the full name of the active document, including the path to the document (for example, z:\Documents\In\Letters\My Letter.docm).
• Line 4 assigns to the variable intToChange the value of the InStr function that finds the string \In\ in the variable strOName. Using the example path from the previous paragraph, intToChange will be assigned the value 13 because the first character of the \In\ string is the 13th character in the strOName string.
• Line 5 assigns to the variable strNName the new filename created in the main part of the statement. This breaks down as follows:
  • Left(strOName, intToChange - 1) takes the left section of the strOName string, returning the number of characters specified by intToChange - 1 - the number stored in intToChange minus one.
  • & "\Out\" adds to the partial string specified in the previous bullet item (to continue the previous example, z:\Documents) the characters \Out\, which effectively replace the \In\ characters, thus changing the directory name (z:\Documents\Out\).
  • & Right(strOName, Len(strOName) - intToChange - 3) completes the partial string by adding the right section of the strOName string, starting from after the \In\ string (Letters\My Letter.docm), giving z:\Documents\Out\Letters\My Letter.docm. The number of characters to take from the right section is determined by subtracting the value stored in intToChange from the length of strOName and then subtracting 3 from the result. Here, the value 3 comes from the length of the string \In\; because the intToChange value stores the character number of the first backslash, you need count only the I, the n, and the second backslash to reach its end.
• Line 6 saves the document using the name in the strNName variable.

The syntax for InStrRev is similar to that of InStr:

InStrRev(stringcheck, stringmatch[, start[, compare]])

These are the arguments:

• stringcheck is a required String argument specifying the string in which to search for stringmatch.
• stringmatch is a required String argument specifying the string for which to search.
• start is an optional numeric argument specifying the starting position for the search. If you omit start, VBA starts at the last character of stringcheck.
• compare (as for InStr) is an optional argument specifying how to search: vbTextCompare for text, vbBinaryCompare for a binary comparison.