TOP

VBA Function: InStr

Description

The VBA INSTR Function returns an integer corresponding to the first position found of a value in a character string (or the value 0 if no match was found).


INSTR Syntax

InStr(start_num, text, value)

Or

InStr(start_num, text, value, case)

VBA InStr Example

Using the InStr Function to determine an "excel" word position (starting searches from the character 1 of the sitename):

Sub InStrExample1()
    
    sitename = "www.moonexcel.com.ua"
    
    'Position of "excel" in the sitename
    position = InStr(1, sitename, "excel")
    
    MsgBox position 'Returns: 9
    
End Sub

Using the InStr Function to determine an "EXCEL" word position (this time adding the value 1 in the 4th argument to ignore case):

Sub InStrExample2()
    
    sitename = "www.moonexcel.com.ua"
    
    'Position of "EXCEL" in the sitename (ignoring case)
    position = InStr(1, sitename, "EXCEL", 1)
    
    MsgBox position 'Returns: 9
    
End Sub

CHECK IF A TEXT CONTAINS A VALUE

The InStr Function can also be used to determine whether or not the sitename contains the search string:

Sub InStrExample3()
    
    sitename = "www.moonexcel.com.ua"
    
    If InStr(1, sitename, "excel") > 0 Then
      MsgBox "Yes!"
    End If
    
End Sub

In this example, if a position was found, a number greater than 0 is returned by the function and the dialog box is displayed.

The YLC Utilities menu in LO Calc

The YLC Utilities menu in LO Calc

The YLC Utilities menu in Excel

The YLC Utilities menu in Excel