How to Use InStr Function in VBA (3 Examples)

While working with Excel VBA codes, we come across 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.

InStr Function of VBA (Quick View)

Download Practice Workbook

VBA InStr Function: Syntax and Argument

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

Argument

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:

• 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.

VBA InStr Function: 3 Examples

Let see a few uses of the InStr function in VBA codes.

1. Deciding whether an Address is an Email Address or Not

Look at the data set below. We have a few contact addresses of some customers below.

Now we will develop a VBA code using the InStr function to identify whether it is an Email Address or not.

VBA 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

See 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.

We insert this formula in the first cell and then drag the Fill Handle.

See, we have identified for each address whether it is an email address or not.

Explanation of the VBA Code

• Function DECISION(string1 As String) creates a function called DECISION with a string argument named string1.
• Dim Position As Integer declares an integer variable named Position.
• Position = InStr(1, string1, "@", 0) 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" 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" 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”.

2. Extracting out the Extension of Some Email Addresses

Now we have a list of some Email Addresses of some customers.

This time we will develop a function to extract out the extension of the email addresses, like whether they have gmail.com or yahoo.com.

VBA Code:

Function EXTENSION(Email As String)

    Dim Position As Integer

    Position = InStr(1, Email, "@", 0)

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

End Function

We have created a function called EXTENSION. It extracts out the extension of any email address.

Let’s apply this function to the data set we have.

Enter this formula in the first cell and then double-click on the Fill Handle.

See, we have extracted the Extension of all the Emails.

Explanation of the VBA Code

• Function EXTENSION(Email As String) creates a new function called EXTENSION, with a string argument named Email.
• Dim Position As Integer declares an integer variable named Position.
• Position = InStr(1, Email, "@", 0) 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)) 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

Finally, we will perform a uniquely different task. This time we have the names of some employees of a company.

We will try to build a function to extract out either the first name or the last name of the employees.

VBA Code:

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

We have created a function called SHORTNAME that extracts out either the first name or the last name from a name.

Let’s apply this formula to our data set.

To extract out the first name, the formula will be:

And to extract out the last names, the formula will be:

Explanation of the Formula

• Function SHORTNAME(Name As String, First_or_Last As Integer) creates a new function called SHORTNAME with one String argument named Name and one Integer Argument named First_or_Last.
• Dim Break As Integer declares a new integer variable named Break.
• Break = InStr(1, Name, " ", 0) 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) 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) 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. Do you have any questions? Feel free to ask us.

Further Readings

• How to use VBA UCASE function in Excel (4 Examples)
• How to Use MsgBox Function in Excel VBA (A Complete Guideline)
• How to Use LCase Function in VBA in Excel (With 4 Examples)
• How to use VBA SPLIT function in Excel (5 Examples)
• How to Use TRIM Function in VBA in Excel (Definition + VBA Code)
• How to Create a Body Mass Index (BMI) Calculator in Excel Using VBA
• Excel VBA ASC() Function – Get ASCII Value of Character