The Instr function in VBA checks to see if a string is contained within another string.  This can be useful when trying to extract information from strings of data contained in Excel cells, for example.

Instr Syntax

The Instr function is made up of 4 parts – the start position of the string you are searching, the actual string you are searching, the string you are searching for, and finally the compare method you are using.

The compare method is optional – you do not need to include this argument.  If you leave out this argument, the default compare method will be used which is vbBinaryCompare.

Function FindString() as Boolean
	FindString = InStr(1,"Find some text in this string", "Find")
End Function

The function above would return TRUE – as the word FIND is in the string “Find some text in this string”.  When the function looks for the string, it will start at the BEGINNING of the string – at position 1 as indicated.

The Start Position

Function FindString() as Boolean
	FindString = InStr(6,"Find some text in this string", "Find")
End Function

In the function above, we have amended the start position to 6.  This means that the function would return false because it would only search the first string from position 6 – and therefore the string to search would now be “ome text in this string“

The VBCompareMethod

The VBCompareMethod allows you to tell the function how to look for the data.  The Instr function is case sensitive when the vbBinaryCompare argument is used – and as that is used by default, the Instr function is therefore always case sensitive unless this argument is specified.

Therefore this function below would return false.

Function FindString() as Boolean
	FindString = InStr(1,"Find some text in this string", "find")
End Function

You can use vbTextCompare instead – which would mean that the function would then NOT be case sensitive.

This function below would then return true even though the “find” is in lower case and the “Find” in the text string to search through, is in Upper Case.

Function FindString() as Boolean
	FindString = InStr(1,"Find some text in this string", "find", vbTextCompare)
End Function

Using Instr with Excel Cells

There may be a time that you wish to see if some Excel cells contain the string you are looking for.  If this is the case, you can use a cell address in your ‘String to search’ rather than actually having a string value in that argument.

Function FindString() as Boolean
	FindString = InStr(1,Range("A1").Value, "find")
End Function

The argument above would return true if the word “find” is contained in cell A1, or false if the word “find” is NOT contained in the cell A1.

This can be useful when you have some data that you might like to replace with other data in an excel sheet and wish to loop through the cells to find and replace the data.

Consider the following sheet.

The account type for some customers is ‘Check’.  We have now had an order from our Head Office to amend this account type to “Current”. We want to create a function that will look through each cell in Column F, and replace “Check” with “Current” in the cell.

First, let us create a function to find a specific string.

Function FindString(strA As String) As Boolean
    FindString = InStr(1, strA, "Check")
End Function

We then need to write a procedure to call the function to check if the string we send to the function contains the word ‘Check’, and if that function returns TRUE, then we wish to replace the word with ‘Current’

Sub AmendAccount()
'this is going to use the Find String function to populate the account
    Dim n As Integer, i As Integer
    n = Range("A5", Range("A5").End(xlDown)).Rows.Count
    Range("F5").Select
    For i = 1 To n
        If FindString(ActiveCell) = True Then
            ActiveCell = "Current"
        End If
        ActiveCell.Offset(1, 0).Select
    Next i
End Sub

When we run the code, the result will be as follows:

Finding a position in a string

In the examples above, we have used a Boolean to find out if the string exists.   You can also use the InstrFunction to find out the POSITION of a particular letter in a string.  If you create a function to return an integer rather than a Boolean value, then the function will return the position of the first letter of the string you are looking for.

Function FindLetter() As Integer
    FindLetter = InStr(1, "Find the Letter", "L")
End Function

In the example above, the FindLetter function would return the value of 10 which is the position of the letter ‘L’ in the “Find the Letter” string.

The InstrRev Function

The InstrRev function is the same as the Instr function, except that it searches from right to left instead of from left to right. In our “Find the Letter” function, this would make no difference as we only have one ‘L’ in the function. However, let’s change the letter to find to an ‘e’.

Function FindLetter() As Integer
    FindLetter = InStr(1, "Find the Letter", "e")
End Function

This function returns an 8 as the first instance of the letter ‘e’ is in position 8.

Function FindLetter() As Integer
    FindLetter = InStrRev(1, "Find the Letter", "e")
End Function

This function returns a 14 – as it starts from the right – so it finds the second ‘e’ in the word ‘Letter’ first.

This article has shown you how to use the InStr and InStrRev functions.  They can be extremely useful in manipulating string values in VBA, in replacing text where necessary or to find text in your Excel workbook or Word Document.   These functions can also be used in Access databases and are extremely useful in data manipulation.

See also:

Checking if a cell contains values (different methods)