How to Use InStr Function in VBA (3 Easy Examples)

While working with Excel VBA codes, we come across a few built-in functions that convert a complex task into a small one-line code. InStr is such a function available in Excel VBA that searches for a particular string within another given string starting from a given position. Today I will be showing how you can use the InStr function in VBA to search for a specific string within another given string.

Table of Contents hide

Download Practice Workbook

Introduction to VBA InStr Function

3 Easy Examples to Use VBA InStr Function

1. Deciding Whether an Address Is an Email Address or Not by Using VBA InStr Function

2. Utilizing VBA InStr Function for Extracting Out the Extension of Some Email Addresses

3. Extracting Out the First or Last Name from a Name by Using VBA InStr Function

Conclusion

Download Practice Workbook

InStr Function.xlsm

Introduction to VBA InStr Function

Summary

Searches for a specific string within a given string, starting from a given position. If finds a match, returns the position in the given string from where the match started.

Syntax

InStr([start],string1,string2,[compare])

Arguments

ARGUMENT	REQUIREMENT	DESCRIPTION
[start]	Optional	The position from which it will start searching. The default is 1.
string1	Required	The string within which it searches for a given string.
string2	Required	The string that it searches for within a given string.
[compare]	Optional	A numeric value between {-1,0,1,2} that specifies the type of comparison. The default is -1 (vbUseCompareOption). If the Option Compare statement is not specified, perform Binary Comparison.

Note:

Though the [start] argument and the [compare] argument are optional, you need the [start] argument if you specify the [compare] argument. Otherwise, it will raise an error.
If either one or both of the [start] argument and the [compare] argument is Null, you will face an error.
The four specified values in the [compare] argument perform four different types of comparison. They are mentioned below:

VALUE	CONSTANT	DESCRIPTION
-1	vbUseCompareOption	Performs the comparison specified in the Option Compare statement.
0	vbBinaryCompare	Performs a Binary comparison.
1	vbTextCompare	Performs a text comparison.
2	vbDatabaseCompare	Performs a comparison based on your database.

Option Compare statement is a statement provided before the main VBA code starts. It specifies whether the code will search for a Binary Comparison or Text Comparison when required.

Option Compare { Binary | Text }

- In simple terms, Binary Comparison means case-sensitive comparison.
- In simple terms, Text Comparison means case-insensitive comparison.
Return Value
- Returns the position in the string1 from which the string2 started.
- In case string2 is not found within string1 starting from the start position, returns 0.
- If the string1 is of zero-length, returns 0.
- If the string2 is of zero-length, returns the argument start.
- And finally, if any of the string1 or string2 is Null, returns an error.

3 Easy Examples to Use VBA InStr Function

In this section, we will see three easy examples of applying the VBA InStr function in Excel. Let’s see the first example of the InStr function in VBA codes.

1. Deciding Whether an Address Is an Email Address or Not by Using VBA InStr Function

Here, we have taken a data set containing some contact addresses of customers. Our goal here will be to identify whether the addresses are email addresses or not.

Now we will develop a VBA code using the InStr function to identify whether it is an Email Address or not. To do that, follow the steps below.

Steps:

In the beginning, go to the Developer tab and select Visual Basic( Or press Alt+F11) to open the VBA window.

Then, on the VBA window, go to Insert > Module.

After that, on the module, paste the following code:

Function DECISION(string1 As String)
    Dim Position As Integer
    Position = InStr(1, string1, "@", 0)
    If Position = 0 Then
        DECISION = "Not Email"
    Else
        DECISION = "Email"
    End If
End Function

Moreover, we have created a function called DECISION. It decides whether an address is an email address or not. Let’s apply the function to the data set we have.
On cell C5, write down the following formula and then autofill the rest of the cells using Fill Handle.

=DECISION(B5)

As we can see, we have identified each address whether it is an email address or not.

🎓How Does the Code Work?

Function DECISION(string1 As String)

First, this creates a function called DECISION with a string argument named string1.

Dim Position As Integer

It declares an integer variable named Position.

Position = InStr(1, string1, “@”, 0)

This assigns the value of the Position variable as the output of the InStr function with the arguments 1, string1, “@” and 0. In short, this assigns the position in the address where there is a “@”.

If Position = 0 Then DECISION = “Not Email”

It assigns the output of the DECISION function as “Not Email”, if the Position variable is 0, that means, there was no “@” in the address.

(Recall if any string is not found within the given string, the InStr function returns 0).

Else DECISION = “Email”

This assigns the output of the DECISION function as “Email” if there was an “@” in the address. Thus the addresses where there is the “@” are categorized as Email, and the rest are categorized as “Not Email”.

Similar Readings

2. Utilizing VBA InStr Function for Extracting Out the Extension of Some Email Addresses

Here, we have a list of some Email Addresses of some customers. This time we will extract the extension of the email address like whether they have gmail.com or yahoo.com.

To do that, follow the steps below.

Steps:

To begin with, like the previous method, open a new VBA module and paste the following code in the window.

Function EXTENSION(Email As String)
    Dim Position As Integer
    Position = InStr(1, Email, "@", 0)
    EXTENSION = Right(Email, (Len(Email) - Position))
End Function

In addition, we have created a function called EXTENSION. It extracts the extension of any email address.
Furthermore, let’s apply this function to the data set we have. First, enter this formula on cell C5 and then double-click on the Fill Handle.

=EXTENSION(B5)

Lastly, we can see that we have successfully extracted the Extension of all the Emails.

🎓How Does the Code Work?

Function EXTENSION(Email As String)

This creates a new function called EXTENSION, with a string argument named Email.

Dim Position As Integer

This part declares an integer variable named Position.

Position = InStr(1, Email, “@”, 0)

This assigns the value of the Position variable as the output of the InStr function with the arguments 1, Email, “@” and 0. In short, this assigns the position in the Email where there is a “@”.

EXTENSION = Right(Email, (Len(Email) – Position))

This part assigns the output of the EXTENSION function as the characters after the symbol “@”. This is the required extension of the Email.

3. Extracting Out the First or Last Name from a Name by Using VBA InStr Function

Finally, we will perform a uniquely different task. This time we have the names of some employees of a company. And we will try to build a function to extract either the first name or the last name of the employees.

To do that, follow the steps below.

Steps:

Firstly, similar to method 1, paste the code below in a new module in the VBA window.

Function SHORTNAME(Name As String, First_or_Last As Integer)
    Dim Break As Integer
    Break = InStr(1, Name, " ", 0)
    If First_or_Last = -1 Then
        SHORTNAME = Left(Name, Break - 1)
    Else
        SHORTNAME = Right(Name, Len(Name) - Break)
    End If
End Function

Here, we have created a function called SHORTNAME that extracts either the first name or the last name from a name. Let’s apply this formula to our data set.
At first, to extract the first name, write down the formula below in cell C5.

=SHORTNAME(B5,-1)

Lastly, to extract the last names, we write down the following formula in D5:

=SHORTNAME(B5,1)

Afterward, if we auto-fill the rest of the cells with Fill Handler, we will see our desired result.

🎓How Does the Code Work?

Function SHORTNAME(Name As String, First_or_Last As Integer)

It creates a new function called SHORTNAME with one String argument named Name and one Integer Argument named First_or_Last.

Dim Break As Integer

This portion declares a new integer variable named Break.

Break = InStr(1, Name, ” “, 0)

It assigns the value of the Break variable as the output of the InStr function with the arguments 1, Name, “ ” and 0. In short, this assigns the position in the Name where there is a space (“ ”).

If First_or_Last = -1 Then SHORTNAME = Left(Name, Break – 1)

This line assigns the output of the SHORTNAME function as the characters before the space, if the First_or_Last argument is -1. This is the first name.

SHORTNAME = Right(Name, Len(Name) – Break)

This part assigns the output of the SHORTNAME function as the characters after the space, if the First_or_Last argument is 1. This is the last name.

Conclusion

In this way, you can write VBA codes with the InStr function which finds out the position of a specific string within another given string, and then you can create your own functions for a variety of uses. Furthermore, if you have any questions, feel free to ask us.