An Explanation on Excel VBA Instr Function

You may want to know how you can apply the bold font format to a specific part of a string that is within a cell.

For example, you can have the bold format applied to just the word ‘Hello’ from the phrase ‘Hello World’, but you want to use this to hundreds of cells. 

However, Excel does not have an inbuilt functionality that can do that.

What you can do is to create a simple macro that makes use of the Excel VBA InStr function. 

Before anything else, you need to see the way the VBA InStr function of Excel works.

Our tutorial will explain how you can use the Excel VBA InStr function, including some practical examples to use it.

VBA InStr Function in Excel 

The InStr function in Excel looks for the specified substring’s position within the string and then returns the initial position when it occurs.

For example, you want to look for ‘x’ and its position in Excel with the Excel VBA InStr function that will return 2.

InStr Function’s Syntax

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

  • String1 – Here is the parent or main string that you want to look for. For example, if you’re going to look for x’s position in Excel, “Excel” will be String1.
  • String2 – Here is the substring that you are looking for. Whenever you look for x’s position in Excel, it would be String2.
  • [Compare] – This is an optional argument where you can have any of these three values specified to compare an argument:
    • vbTextCompare – What this does is a textual comparison. For example, if you search for the ‘X’ or ‘x’ in Excel, both cases will return 2. The letter case is ignored in this argument as you can also have 1 used aside from vbTextCompare.
    • vbDatabaseCompare – It is only used for Microsoft Access. Here, it uses database information to make a comparison. Instead of the vbDatabaseCompare, you can also have 2 used.
    • vbBinaryCompare – Involved here is a comparison of one character to another. For example, if you are searching for an ‘x’ in ‘Excel.’ Since X is in the upper case, it will return 0. Instead of vbBinaryCompare, you can also have 0 used instead. It is taken as default whenever the [Compare] argument gets omitted.
    • [Start] – Here is an optional argument that is an integer value. It tells about the InStr function the beginning position from where it should begin to search. For example, if you want the search to start initially, you need to enter the 1 value. Whenever you want to start from the third character onwards, 3 is what you will use. If it gets omitted, it takes the default value of 1.

Added Notes on the VBA InStr Function of Excel:

  • Whenever the substring whose position you are searching for or String2 becomes empty, the function will have [Start] argument’s value returned.
  • If the InStr function can’t find the substring in the main string, 0 will return.
  • The VBA function InStr is not a worksheet function as you can’t use it in the worksheet.

Here are some of the examples of how you can use the Excel VBA InStr Function:

Example 1 – Highlight a Part of the String within the Cells

People may have been wondering how to make the numbers outside of this bracket bold:

This is the code that I made that can do 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

With this code, the For Each loop is used to go through every cell in the selection.

Identified here is the opening character bracket’s position with the use of the InStr function.

Then, the text font is changed before the bracket.

If you want to have this code used, you have to use the VB editor to copy and paste this into a module.

After you copy-pasted the code, choose the cells where you want this formatting to apply and have the macro run as shown here:

Example 2 – From the Start, Look for the Position

Here in this example, you will have the InStr function to look for ‘V’ and its position in ‘Excel VBA’ from the start.

Here is the code for this:

Sub FindFromBeginning()

Dim Position As Integer

Position = InStr(1, “Excel VBA”, “V”, vbBinaryCompare)

MsgBox Position

End Sub

Once you run this code, it will show the message box. It has the value 7.

In the ‘Excel VBA’ string, it is in the ‘V’ position.

Example 3 – Look at the Position from the Second Word’s beginning 

Assume you want to search for the placement of ‘the’ in this sentence: ‘My parents and I watched a movie.’

However, what you want to do is to start the search from the second word onwards.

For this case, you have to make changes to the [Start] argument to ensure that the position from where the second word begins is specified.

You can do it with this code:

Sub FindFromSecondWord()

Dim Position As Integer

Position = InStr(4, “My parents and I watched a movie”, “my”, vbBinaryCompare)

MsgBox Position

End Sub

This code will show the value 32 in the message box as what is specified in the beginning position as 4.

Because of that, it ignores the initial ‘The’ and looks for the sentence’s second ‘the.’

If you want it to become more dynamic, have the code enhanced so you can have the first word ignored automatically.

You can do this with this enhanced code:

Sub FindFromSecondWord()

Dim StartingPosition As Integer

Dim Position As Integer

StartingPosition = InStr(1, “The five boxing wizards jump quickly”, ” “, vbBinaryCompare)

Position = InStr(StartingPosition, “The five boxing wizards jump quickly”, “the”, vbBinaryCompare)

MsgBox Position

End Sub

First, this code looks for the space character’s position and allows it to get stored in the variable StartingPosition.

After that, searching for the word ‘the,’ uses the variable as the beginning position.

Because of that, 32 is returned that is the beginning of ‘the’ after the starting word.

Example 4 – In an Email Address, look for the Position of @

Allow yourself to quickly make a custom function so you can look for @ in an email address using the function Excel VBA InStr.

Make the custom function by using this code:

Function FindPosition(Ref As Range) As Integer

Dim Position As Integer

Position = InStr(1, Ref, “@”)

FindPosition = Position

End Function

By now, you can have this custom function used as any other function in the worksheet.

Here, the cell reference will be taken as input that will provide you with the position of @.

In the same way, you can make a customized function to look for any substring’s position in the main string.

Get to Know How You Can Use VBA Instr Excel with ExcelMaster

Make yourself an Excel expert with us!

Let yourself become knowledgeable in using the VBA Instr function in Excel with ExcelMaster NOW!

Leave a Comment