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

Get FREE Advanced Excel Exercises with Solutions!

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.


Download Practice Workbook


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.

VBA INSTR Function

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

Deciding Whether an Address Is an Email Address or Not by Using VBA InStr 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)

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

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

Read More: How to Use VBA InStrRev Function (7 Suitable Examples)


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.

Extracting Out the Extension of Some Email Addresses by Using VBA InStr Function

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

Extracting Out the Extension of Some Email Addresses by Using VBA InStr 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)

Extracting Out the Extension of Some Email Addresses by Using VBA InStr Function

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

Read More: Create Custom VBA functions and Use them in the Worksheet


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

Extracting out the First or Last Name from a Name

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

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

  • Lastly, to extract the last names, we write down the following formula in D5:
=SHORTNAME(B5,1)

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

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

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

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

Read More: How to Use VBA Function Procedure with Arguments in Excel


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.


Related Articles

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo