VBA InStr

InStr Function In Excel VBA

VBA InStr is a text function that helps us to find the position of the desired alphabet or number within the given text value. InStr stands for “In String” in VBA excel.

For example, if we want to find the position of the alphabet ‘s’ in the text, ‘Wallstreetmojo’, the InStr function will return the position of the text ‘s’ as 5.

Meanwhile, VBA InStr function is widely used along with other text functions as a supporting function to retrieve the substring from the full string based on various positions.

Remember, the output of the InStr function is always an integer number.

Syntax Of VBA InStr Function

It is important to understand the syntax of any function before we implement them practically. The following is the syntax of the InStr function in Excel VBA.

InStr([Start],[String1],[String2],[Compare as VbCompareMethod = vbBinaryCompare]

[Start]: First, we need to specify from which position of the string InStr function should start to search for the given text value. By default, the start position is always 1.

For example, if we enter the [start] position as 3 to search for the text ‘e’ in the word ‘Excel’, the InStr function will return 4 as the output. The letter is ‘E’ but since we have given the starting position as 3 the InStr function will start to look for the letter ‘E’ only after the 3^rd character of the given text, so the result will be 4.

[String 1]: In this argument, we need to give the text value from which we need to find the substring. For example, ‘Excel’ is the [String 1].

[String 2]: From the [String 1] what substring we are looking to find the position of. So, the letter ‘E’ is the substring position we are looking to find.

[Compare]: In this argument, we need to specify the comparison type. There are 3 types of comparison methods available and those are:

1. vbBinaryCompare
2. vbDatabaseCompare
3. vbTextCompare

vbBinaryCompare: This is used to find case-sensitive search of characters. Remember, this is a case-sensitive search of a substring (string 2) in the full string (string 1).

Now, if we enter 0 as the vbBinaryCompare following are the example outputs.

• If we want to find the character ‘y’ (String 2) in the string ‘Sydney’ (String 1) with the binary search input as 0, then it will return the result as 1, because the letter ‘y’ position in the string 1 ‘Sydney’ is 2.
• Similarly, if we want to find the character ‘Y’ (String 2) in the string ‘Sydney’ (String 1) with the binary search input as 0, then it will return the result as 0, because the letter ‘Y’ is upper case and search we have given is case-sensitive one and upper case ‘Y’ is not there in the string 1 ‘Sydney’.

vbTextCompare: This option can be used as ignorance of case-sensitive searches. Now, we can pass 1 as the input argument.

If we enter 1 as the vbTextCompare, the following are the example outputs.

• If we want to find the character ‘y’ or ‘Y’ (String 2) in the string ‘Sydney’ (String 1) with the text search input as 1, then it will return the result as 2 in both the instances. It is a non-case-sensitive search.

vbDatabaseCompare: This is specifically used for Microsoft Access only. Here, we can enter 2 instead of vbDatabaseCompare.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

VBA InStr Function Examples

Example #1 – Find The Position Of The Specified Character

We will use the word ‘Abacus Learning’ to find the position of the letter ‘a’ at various positions.

• Step 1: First, start the subroutine procedure by giving a name to the macro.

• Step 2: Next, define a variable to assign the result of the InStr function.

• Step 3: Then, define another variable to assign the text value.

• Step 4: Now, define one more variable to assign the targeted substring.

• Step 5: Next, assign the word ‘Abacus Learning’ for the variable ‘Actual_String’.

• Step 6: Similarly, assign the word ‘A’ to the variable ‘Required_String’.

• Step 7: Now, for the variable, we will assign the value given by the InStr function.

• Step 8: Next, give the start position as 1.

• Step 9: Then, for string 1 give the variable name ‘Actual_String’.

• Step 10: Now, for string 2 give the variable name ‘Required_String’.

• Step 11: Similarly, for the compare method, let’s use 0 or vbBinaryCompare.

• Step 12: Next, assign the variable name to the message box.

The following is the complete code.

Sub Instr_Function()

Dim k As Integer
Dim Actual_String As String
Dim Required_String As String

Actual_String = "Abacus Learning"

Required_String = "a"

k = InStr(4, Actual_String, Required_String, vbBinaryCompare)

MsgBox k

End Sub

Now, when we run this code, it will return the result as 1.

Now, the string that we have supplied is ‘A’ and we have given the binary comparison for case-sensitive search.

In the word ‘Abacus Learning’ the upper case letter ‘A’ is in the first position, hence the InStr function returns the value as 1.

• Change the letter A from upper case to lower case: Now, let us change the letter from upper case to lower case.

Now run the code and it will give the following result.

Now the result is 3 i.e., the first letter A is not considered to find the position. Hence, returned the 3^rd instance in the word ‘Abacus Learning’.

• Change the starting position: By default, InStr takes the starting as 1, let us change this to 4 in the existing code.

Next, run this code, and we will get the following result.

Now, the result is 10.

In the word ‘Abacus Learning’ the position of the letter ‘a’ starting from the 4^th position is 10.

Example #2 – Hide All Similar Worksheets

Now, let us give you some of the advanced examples of using the InStr function in VBA. For example, look at the following worksheets in the Excel workbook.

We have many worksheets in the Excel workbook. Now, we need to hide all the worksheets which contain the word ‘Month’ and do not hide worksheets that don’t have the word ‘Month’.

Following is the VBA code that helps us to hide all the worksheets with the name ‘Month’.

Sub Instr_Example_Hide_Worksheets()

Dim Ws As Worksheet
'WS = Worksheet

For Each Ws In ActiveWorkbook.Worksheets

    If InStr(Ws.Name, "Month") > 0 Then
        Ws.Visible = xlSheetHidden
    End If
Next Ws

End Sub

Now, let us explain the code to you.

First, we have declared the variable ‘Ws’ and assigned the object data type as a worksheet.

Next, we have initiated the FOR EACH loop to loop through all worksheets.

Inside the FOR EACH loop, we have used the IF logical statement along with the InStr function to check for the worksheet name. Here, the InStr function will check the worksheet name and finds if it has the word ‘Month’, if the worksheet name has the word ‘Month’ then the InStr function will return the position of the ‘Month’ word in the full worksheet name, so the number will be greater than 0.

Now, whenever the InStr function returns a number greater than 0, then it will hide that worksheet.

Next, run this code and it should hide all the worksheets with the name ‘Month’.

Similarly, if we want to unhide all the with the worksheet name ‘Month’ then we can use the following code.

Sub Instr_Example_UnHide_Worksheets()

Dim Ws As Worksheet
'WS = Worksheet

For Each Ws In ActiveWorkbook.Worksheets

    If InStr(Ws.Name, "Month") > 0 Then
        Ws.Visible = xlSheetVisible
    End If
Next Ws

End Sub

Now, when we run this code, it will unhide all the hidden worksheets which have the word ‘Month’ in them. If any other worksheet is hidden without the name ‘Month’, it won’t unhide them.

Example #3 – Extract First Name And Last Name

Extracting first and last names from the full name is a common task we all have in worksheet functions.

For instance, we have the following data in Excel.

Next, we have full names in column A and in columns B and C we need to extract the first name and last name respectively. Now, look at the following code:

Sub Instr_Example_FirstName_LastName()

Dim k As Integer
Dim LR As Integer
Dim Space As Integer
Dim Lenght As Integer

LR = Cells(Rows.Count, 1).End(xlUp).Row

For k = 2 To LR
    Space = InStr(Cells(k, 1).Value, " ")
    Lenght = Len(Cells(k, 1).Value)
    Cells(k, 2).Value = Left(Cells(k, 1).Value, Space - 1)
    Cells(k, 3).Value = Right(Cells(k, 1).Value, Length - Space)
Next k

End Sub

Now, let us explain the code to you.

• Variable Declaration: We have declared some variables and those are:

Dim k As Integer – This is to apply the loop to go through all the cells.

Dim LR As Integer – This is to find the last used row in column A.

Dim Space As Integer – This is to find the space character position in the given text.

Dim Lenght As Integer – This is to find the total characters of the given text.

• Find the Last Used Row: Next, we are finding the last used row number assigning the same to the variable LR.

• Initiate the Loop: Next, we are initiating the loop to go through all the cells.

Then, using the variable ‘k’ we have initiated the loop with starting position as 2 and the ending position is equal to the last use row (LR).

• Find the Space Character in the Text: Now, for the variable ‘Space’ we need to find the position of the space character from the current looping cell.

• Find a total character in the Text: Next, we are finding the total number of characters in the given text and assigning it to the variable ‘Length’.

• Extract First Name: Next we are extracting the first name by using the LEFT function. The LEFT excel function will extract the characters from the left side of the text. How many characters we need to extract is based on the space character.

Now, we have already found the space position in the text value by using the InStr function. Since we do not need a space character in the first name, we have applied -1.

• Extract Last Name: Next we are extracting the last name by using the RIGHT function. The RIGHT excel function will extract values from the right side of the text.

Now, for this, we are deducting the space character position from the overall length of the text value.

When we run this code, we will get the first name and last name.

Important Things To Note

• InStr is a VBA function only. If you are looking for the function in Excel as a worksheet function, then it won’t be available.
• If String 2 is not available in String 1, then InStr will return 0 as the result.
• InStr is a case-sensitive function, so to ignore this we need to give vbTextCompare for the last argument of the InStr function.
• By default, the starting position is always from 1. We need to mention the starting number manually if we need the search to happen after a specific number of characters.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the VBA InStr, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to VBA InStr. Here we learn how to use the VBA InStr Function in Excel with its syntax, examples & downloadable excel template. You can learn more from the following articles –

• VBA String Functions
• VBA Dictionary
• VBA Last Row