Searches for a given occurrence of a string, from the left or the right.
FoundPos := InStr(Haystack, Needle , CaseSense := false, StartingPos := 1, Occurrence := 1)
The string whose content is searched.
The string to search for.
Type: Integer or String
One of the following values (defaults to 0 if omitted):
True): The search is case sensitive.
False): The letters A-Z are considered identical to their lowercase counterparts.
"Locale": The search is case insensitive according to the rules of the current user's locale. For example, most English and Western European locales treat not only the letters A-Z as identical to their lowercase counterparts, but also non-ASCII letters like Ä and Ü as identical to theirs. Locale is 1 to 8 times slower than Off depending on the nature of the strings being compared.
Omit StartingPos to search the entire string. Otherwise, specify the position at which to start the search, where 1 is the first character, 2 is the second character, and so on. Negative values count from the end of Haystack, so -1 is the last character, -2 is the second-last, and so on.
If Occurrence is omitted, a negative StartingPos causes the search to be conducted from right to left. However, StartingPos has no effect on the direction of the search if Occurrence is specified.
For a right-to-left search, StartingPos specifies the position of the last character of the first potential occurence of Needle. For example,
InStr("abc", "bc",, 2, +1) will find a match but
InStr("abc", "bc",, 2, -1) will not.
If the absolute value of StartingPos is greater than the length of Haystack, 0 is returned.
If Occurrence is omitted, it defaults to the first match of Needle in Haystack. The search is conducted from right to left if StartingPos is negative; otherwise it is conducted from left to right.
If Occurrence is positive, the search is always conducted from left to right. Specify 2 for Occurrence to return the position of the second match, 3 for the third match, etc.
If Occurrence is negative, the search is always conducted from right to left. For example, -2 searches for the second occurrence from the right.
This function returns the position of an occurrence of the string Needle in the string Haystack. Position 1 is the first character; this is because 0 is synonymous with "false", making it an intuitive "not found" indicator.
Regardless of the values of StartingPos or Occurrence, the return value is always relative to the first character of Haystack. For example, the position of "abc" in "123abc789" is always 4.
Conventionally, an occurrence of an empty string (
"") can be found at any position. However, as a blank Needle would typically only by passed by mistake, it is treated as an error (an exception is thrown).
A ValueError is thrown in any of the following cases:
RegExMatch can be used to search for a pattern (regular expression) within a string, making it much more flexible than InStr. However, InStr is generally faster than RegExMatch when searching for a simple substring.
InStr searches only up to the first binary zero (null-terminator), whereas RegExMatch searches the entire length of the string even if it includes binary zero.
RegExMatch, Is functions
MsgBox InStr("123abc789", "abc") ; Returns 4
Haystack := "The Quick Brown Fox Jumps Over the Lazy Dog" Needle := "Fox" If InStr(Haystack, Needle) MsgBox "The string was found." Else MsgBox "The string was not found."
Haystack := "The Quick Brown Fox Jumps Over the Lazy Dog" Needle := "the" MsgBox InStr(Haystack, Needle, false, 1, 2) ; case insensitive search, return start position of second occurence MsgBox InStr(Haystack, Needle, true) ; case sensitive search, return start position of first occurence, same result as above