Home » VBA-Functions » VBA-InStr-Function

VBA InStr Function

Related Function:
VBA InStrRev

Description

The Excel VBA InStr function returns the position of a substring within a string, as an integer.

If the substring is not found, the function returns the value 0.

The syntax of the InStr function is:

InStr( [Start], String1, String2, [Compare] )

Where the function arguments are:

[Start] -

An optional integer argument, representing the position that you want to start searching from.

If omitted, the [Start] argument takes on the default value of 1.
String1 - The string that you want to search.
String2 - The substring that you want to search for.
[Compare] -

An optional argument, specifying the type of comparison to make.

This can be any of the following values:

vbBinaryCompare - performs a binary comparison
vbTextCompare - performs a text comparison
vbDatabaseCompare - performs a database comparison
If omitted, the [Compare] argument takes on the default value vbBinaryCompare.


VBA InStr Function Examples

Example 1

' Example 1. Find the text "st" within the string "Test string", starting from position 1.
Dim pos As Integer
pos = InStr( "Test string", "st" )
' pos now has the value 3.

In the example above:


Example 2

' Example 2. Find the text "st" within the string "Test string", starting from position 5.
Dim pos As Integer
pos = InStr( 5, "Test string", "st" )
' pos now has the value 6.

In the example above, the search begins at position 5 of the string "Test string" and so the function finds the second occurrence of "st" at position 6.


Example 3

' Example 3. Find the text "t" within the string "Test string", starting from position 1.
Dim pos As Integer
pos = InStr( 1, "Test string", "t" )
' pos now has the value 4.

In the above example, the InStr function returns the position of the first lower case "t", which is position number 4.

Note that, as the [Compare] argument is omitted, it is set to the default value vbBinaryCompare (binary comparison), which is case sensitive.


Example 4

' Example 4. Find the text "ZZ" within the string "Test string", starting from position 1.
Dim pos As Integer
pos = InStr( 1, "Test string", "ZZ" )
' The text "ZZ" is not found and so the function returns the value 0.

In the above example, the sub-string "ZZ" is not found and so the InStr function returns the value 0.


Further information and examples of the VBA InStr function are provided on the Microsoft Developer Network.

Return to the List of VBA Functions
Return to the ExcelFunctions.net Home Page