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

Returns the position of a substring within a larger string (Long).

start(Optional) The character position to start searching from (Integer) (1).
string1The text string containing the text you want to find (String).
string2The substring to look for (String).
compare(Optional) A vbCompareMethod constant specifying the type of string comparison to use (Integer):
-1 = vbUseCompareOption (uses the "Option Compare" setting)
0 = vbBinaryCompare (case sensitive) (default)
1 = vbTextCompare (not case sensitive)
2 = vbDatabaseCompare (uses an Access database)

* This function is case sensitive (by default).
* This function does not support wildcards (? and *).
* If "start" is left blank, then 1 is used. (i.e. you start searching from the first character).
* If "start" is 0, then a run-time error occurs.
* If "start" > 1, the position returned is still the position from the first character (not the start position).
* If "start" is Null, then an error occurs.
* If "string1" cannot be found in "string2" then 0 is returned.
* If "string1" = "string2" or "string1" is the beginning of "string2" then 1 is returned.
* If "compare" is left blank, then -1 is used. If there is no Option Compare statement provided then vbBinaryCompare (0) is used.
* If "compare" is Null, then an error occurs.
* You can use the INSTRREV function to return the position of a substring within a larger string, starting at the end.
* You can use the INSTR$ function to return a String data type instead of Variant data type.
* If you type in INSTR$ with valid arguments the editor will automatically change this function to just INSTR.
* You can use the INSTRB function that is used with byte data.
* You can use the MID Function to return a substring from the middle, left or right of a string.
* You can use the LIKE Operator to pattern match using wildcards.
* For more information, refer to the Finding Strings page.
* The equivalent .NET function is Microsoft.VisualBasic.Strings.InStr
* For the Microsoft documentation refer to

Debug.Print InStr("C:\Temp\", "C:\")               '= 1  
Debug.Print InStr(1, "C:\Temp\", "C:\") '= 1
Debug.Print InStr(1, "C:\Temp\", "E:\") '= 0
Debug.Print InStr(1, "somemoretext", "more") '= 5
Debug.Print InStr("somemoretext", "more") '= 5
Debug.Print InStr(4, "somemoretext", "more") '= 5 always counts from position 1
Debug.Print InStr("somemoretext", "somemoretext") '= 1
Debug.Print InStr("somemoretext", "nothing") '= 0
Debug.Print InStr("bettersolutions", "better") '= 1
Debug.Print InStr("bettersolutions", "solutions") '= 7

Dim lPosition As Long
lPosition = InStr("Monday", "day")
Debug.Print lPosition '= 4

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited Top