Searching Strings
Another function can search a string for a specified substring. This function is called Instr, which stands for "in string." The syntax for this is fairly complicated because two of the arguments are optional:
InStr([start, ]string1, string2[, compare])
Start is an optional parameter and shows where in the string the search should start from. If it is omitted, the search starts from position 1. Start must not contain a null value, and if start is used, then the Compare parameter must be used.
String1 is the string being searched (for example, "Martin Cane"). String2 is the string being sought (for example, "cane").
Compare is the technique used to compare the strings. The possible values are vbBinaryCompare and vbTextCompare. In simple terms, this determines whether the search is case sensitive or not. Binary compare uses the actual binary value, so A equals A, but A does not equal a. Text compare ignores case, so A will equal a. A null value here will produce an error. The default for Compare is binary, which means it is case-sensitive. Table 5-1 lists the values the Instr function produces. Here is a simple example:
x=Instr("Martin Cane","Cane")
This will give an answer of 9.
Note that the default compare flag is binary/case sensitive:
x = Instr("Martin Cane","Cane")
Table-1 Values of the Instr Function
Value Returned by Instr Meaning 0 String1 is zero length. Null String1 is null. Start Value String2 is zero length. Null String2 is null. 0 String2 not found. Position Position of String2 within String1. 0 Start is greater than length of String1.
This will give the answer 0 because the string "cane" is not found due to the difference in case. The following will give the answer 9:
MsgBox InStr(1, "Martin Cane", "cane", vbTextCompare)
The next example uses the two optional parameters. Notice the use of the start position:
MsgBox InStr(10, "Martin Cane", "cane", vbTextCompare)
This will give the result 0 (string not found) because the start search position is after where the search string is found.
The InstrRev function can be used to do a reverse search through a string. This means that it begins the search from the end of the string instead of the beginning.