Excel VBA InStr Function – Explained with Examples

Yesterday, I got an email from one of my readers – June.

She wanted to know how to apply bold font format to a specific part of a string within a cell. For example, apply the bold format to only the word ‘Hello’ from ‘Hello World’.

And she wanted to do this for hundreds of cell at once.

Since there is no inbuilt functionality in Excel that can do that, I created a simple macro that uses the Excel VBA InStr function (you will see how to do this in Example 4 in this tutorial).

But first, let’s see how the Excel VBA InStr function works!

Excel VBA InStr Function

In this tutorial, I will explain the usage of InStr function in Excel VBA and see some practical examples where it can be used.

Excel VBA InStr Function – Introduction

InStr function finds the position of a specified substring within the string and returns the first position of its occurrence.

For example, if you want to find the position of ‘x’ in ‘Excel’, using the Excel VBA InStr function would return 2.

Syntax of InStr Function

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

• [Start] – (optional argument) this is an integer value that tells the InStr function the starting position from which it should start looking. For example, if I want the search to start from the beginning, I will enter the value as 1. If I want it to begin with the third character onwards, I will use 3. If omitted, the default value of 1 is taken.
• String1 – This is the main string (or the parent string) in which you want to search. For example, if you’re looking for the position of x in Excel, String 1 would be “Excel”.
• String2 – This is the substring that you are searching for. For example, if you’re looking for the position of x in Excel, String2 would be x.
• [Compare] – (optional argument) You can specify one the following three values for [compare] argument:
  • vbBinaryCompare – This would do a character by character comparison. For example, if you’re looking for ‘x’ in ‘Excel’, it will return 2, but if you’re looking for ‘X’ in ‘Excel’, it will return 0 as X is in upper case. You can also use 0 instead of vbBinaryCompare. If the [Compare] argument is omitted, this is the taken as default.
  • vbTextCompare – This would do a textual comparison. For example, if you look for ‘x’ or ‘X’ in Excel, it would return 2 in both the cases. This argument ignores the letter case. You can also use 1 instead of vbTextCompare.
  • vbDatabaseCompare –  This is used for Microsoft Access only.  It uses the information in the database to perform the comparison. You can also use 2 instead of vbDatabaseCompare.

Additional Notes on Excel VBA InStr Function:

• InStr is a VBA function and not a worksheet function. This means that you can not use it within the worksheet.
• If String2 (which is the substring whose position you’re looking for) is empty, the function would return the value of the [Start] argument.
• If the InStr function can not find the substring within the main string, it would return 0.

Now let’s have a look at some example of using the Excel VBA InStr Function

Example 1 – Finding the Position from the beginning

In this example, I will use the InStr function to find the position of ‘V’ in ‘Excel VBA’ from the beginning.

The code for this would be:

Sub FindFromBeginning()
Dim Position As Integer
Position = InStr(1, "Excel VBA", "V", vbBinaryCompare)
MsgBox Position
End Sub

When you run this code, it will show a message box with the value 7, which is the position of ‘V’ in the string ‘Excel VBA’.

Example 2 – Finding the Position from the beginning of the Second Word

Suppose, I want to find the position of ‘the’ in the sentence – ‘The quick brown fox jumps over the lazy dog’

However, I want the search to begin with the second word onwards.

In this case, we need to change the [Start] argument to make sure it specifies the position from where the second word starts.

Here is the code that will do this:

Sub FindFromSecondWord()
Dim Position As Integer
Position = InStr(4, "The quick brown fox jumps over the lazy dog", "the", vbBinaryCompare)
MsgBox Position
End Sub

This code will show the message box with the value 32 as we have specified the starting position as 4. Hence it ignores the first ‘The’ and finds the second ‘the’ in the sentence.

If you want to make it more dynamic, you can enhance the code so that it automatically ignore the first word.

Here is the enhanced code that will do this:

Sub FindFromSecondWord()
Dim StartingPosition As Integer
Dim Position As Integer
StartingPosition = InStr(1, "The quick brown fox jumps over the lazy dog", " ", vbBinaryCompare)
Position = InStr(StartingPosition, "The quick brown fox jumps over the lazy dog", "the", vbBinaryCompare)
MsgBox Position
End Sub

This code first finds the position of a space character and stores it in the variable StartingPosition.

It then uses this variable as the starting position to look for the word ‘the’.

Hence it returns 32 (which is the starting position of ‘the’ after the first word).

Example 3 – Finding the Position of @ in Email Address

You can easily create a custom function to find the position of @ in an email address using the Excel VBA InStr function.

Here is the code to create the custom function:

Function FindPosition(Ref As Range) As Integer
Dim Position As Integer
Position = InStr(1, Ref, "@")
FindPosition = Position
End Function

Now you can use this custom function as any other worksheet function. It will take a cell reference as input and give you the position of @ in it.

Similarly, you can create a custom function to find the position of any substring within the main string.

Example 4 – Highlighting a Part of String within Cells

This is the query that was asked by June (my reader who also inspired me to write this tutorial).

Here is a sample data in the format June sent me:

Her query was to make the numbers outside the bracket bold.

Here is the code I created that does this:

Sub Bold()
Dim rCell As Range
Dim Char As Integer
For Each rCell In Selection
 CharCount = Len(rCell)
 Char = InStr(1, rCell, "(")
 rCell.Characters(1, Char - 1).Font.Bold = True
Next rCell
End Sub

The above code uses the For Each loop to go through each of the cells in the selection. It identifies the position of the opening bracket character using the InStr function. It then changes the font of the text before the bracket.

To use this code, you need to copy and paste in a module in the VB editor.

Once you have copy pasted the code, select the cells in which you want to do this formatting and run the macro (as shown below).

You May Also Like the following Excel VBA Tutorials:

• Excel VBA SPLIT Function.
• VBA TRIM Function.
• The Ultimate Guide to Excel VBA Loops.
• A Beginner’s Guide to Using For Next Loop in Excel VBA.
• How to Create and Use an Excel Add-in.
• How to Combine Multiple Workbooks into One Excel Workbook.
• How to Count Colored Cells in Excel.
• Useful Excel VBA Macro Examples for Beginners.
• How to Sort Data in Excel using VBA (A Step-by-Step Guide)