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.

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

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.

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.

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

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

Similar Readings

• How to Use VBA DateDiff Function in Excel (9 Examples)
• Random Number Excel Formula (5 Examples)
• How to Use VBA Str Function in Excel (4 Examples)
  
• Use VBA ChDir Function in Excel (4 Suitable Examples)
• How to Use VBA IsError Function (6 Examples)

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.

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

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

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

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

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

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