How to use the VBA INSTR Function (Syntax + Example)

HomeVBA Functions LIST (Category Wise)How to use the VBA INSTR Function (Syntax + Example)

The VBA INSTR function is listed under the text category of VBA functions. When you use it in a VBA code, it returns the starting position number of a sub-string (specified by you) from another string. In simple words, it works like the FIND and SEARCH (worksheet functions).

Syntax

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

Arguments

  • [Start]: An integer to specify the position from where you want to start the search [This is an optional argument and if omitted VBA takes 1 by default].
  • String1: The string in which you want to search.
  • String2: The string that you want to search for.
  • [Compare]: A string value to define the comparison to make while filtering array [This is an optional argument and if omitted VBA takes vbBinaryCompare by default].
    • vbBinaryCompare: For binary comparison.
    • vbTextCompare: For text comparison.
    • vbDatabaseCompare: For Database Comparison.

Example

To practically understand how to use VBA INSTR function, you need to go through the below example where we have written a vba code by using it:

Sub example_INSTR()
Range("B1").Value = InStr(Range("A1"), " ")
End Sub

In the above code, we have used the INSTR to find the space within the value (Mick Griffin) in cell A1 and it has returned 5 as a result, there’s the first space is between “Mick” and “Griffin” whose position is 5.

Notes

Below are some important points which you need to take care while using INSTR function in VBA.

  • If “string1” cannot be found in “string2” then it will 0 is the result.

Related Functions

FORMAT | INSTRREV | LCASE | LEFT | LEN | LTRIM | MID | REPLACE | RIGHT | RTRIM | SPACE | STRCOMP | STRCONV | STRING | STRREVERSE | TRIM | UCASE