InStr VBA function – How to return character position in text / string

Last Updated on January 11, 2023 by token

1. InStr VBA function – Description

The InStr VBA function returns the position of a character in a string. If we want which position in a given text a character or word occupies, we should use this function. This function can also be used to search for words in the text and cut them out in conjunction with the Mid VBA function . If we want to find the position of a string in a given text starting from the end, we should use the InstrRev function .

2. VBA InStr function – Syntax

'officeinside.org 
InStr ([Start], [String1], [String2], [Compare As VbCompareMethod = vbBinaryCompare])

Start : The position in the text from which to start the search.

String1 : The text string we’re looking for the string in.

String2 : Search string.

Compare As VbCompareMethod : Optional argument. The type of comparison. The default value is 0. The values ​​for the argument are presented in the table below:

Function return : Number / Numeric value.

3. VBA InStr function – Example

How to use InStr  function  in VBA Excel? Below are examples of using the InStr function in the VisualBasic Editor.

Example 1 : An example of using the InStr VBA function – result in the MsgBox window.

'officeinside.org 
Dim strValue As String
strValue = "Learn VBA Functions"
MsgBox InStr (1, strValue, "f")
MsgBox InStr (1, strValue, "a")
MsgBox InStr (1, strValue, "A", vbBinaryCompare)
MsgBox InStr (1, strValue, "A", vbTextCompare)

Example 2 : VBA InStr – function example – result in an Excel sheet.

'officeinside.org
Dim strValue2 As String
strValue2 = "Learning VBA functions"
Range ("A1") = InStr (6, strValue2, "f")
Range ("A2") = InStr (6, strValue2, "a")
Range ("A3") = InStr (6, strValue2, "A", vbBinaryCompare)
Range ("A4") = InStr (6, strValue2, "A", vbTextCompare)

Example 3 : VBA InStr – How to extract text from other text. Text between two spaces.

'officeinside.org
Dim strValue3 As String
Dim intValue1 As Integer
Dim intValue2 As Integer

strValue3 = "Learn VBA functions"
intValue1 = InStr (1, strValue3, "")
intValue2 = InStr (InStr (1, strValue3, "") + 1, strValue3, "")

MsgBox Mid (strValue3, intValue1, intValue2 - intValue1)
Range ("A1") = Mid (strValue3, intValue1, intValue2 - intValue1)

4. VB InStr function – Additional information

• none

5. InStr VisualBasic function – Where to use?

The function can be used in: Excel 2003, Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021, Excel 365.

The article is part of the VBA Excel function list. You can find a list of all VBA functions at this address:  VBA functions.