VBA Tip: InStr

The InStr function checks whether or not a string contains a defined value and returns the position of the first value found (or 0 if the value was not found in the string).

Use:

InStr(start, string, search_value)

or

InStr(start, string, search_value, compare)

Example

Searching for the position of the GA value in the chain:

Sub example()
    
    myString = "4571-GA12-U5-EN-GA15"
    
    'First position of "GA"
    MsgBox InStr(1, myString, "GA") 'Returns : 6

End Sub

To search for the position of the second GA value, change the "start" argument to begin the search further down the chain:

Sub example()
    
    myString = "4571-GA12-U5-EN-GA15"
    
    'First position of "GA"
    firstPos = InStr(1, myString, "GA") 'Value: 6
    
    'Second position of "GA"
    MsgBox InStr(firstPos + 1, myString, "GA") 'Returns: 17

End Sub

Finding out if a string contains a value

It's not always necessary to know the position of the value. Sometimes you only need to check if the string contains the desired value.

In this case, it's even easier:

Sub example()
    
    myString = "4571-GA12-U5-EN-GA15"
    
    If InStr(1, myString, "GA") Then
        MsgBox "Yes!"
    Else
        MsgBox "No..."
    End If
    
End Sub

As a bonus, the shortened version with the IIf function:

Sub example()
    
    myString = "4571-GA12-U5-EN-GA15"
    
    MsgBox IIf(InStr(1, myString, "GA"), "Yes!", "No...")
    
End Sub