How to Use InStr Function in VBA (3 Examples)

Quick View of the InStr Function

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)

Quick View of the InStr Function

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

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

Argument

Argument Required or Optional Value
[start] Optional The position from which it will start searching. 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. Default is -1 (vbUseCompareOption). If the Option Compare statement is not specified, performs 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.

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.

Contact Addresses of Some Customers

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

VBA Code for Identifying Email Addresses

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.

=DECISION(B4)

Decision Formula with Function Having InStr in VBA Code

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.

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

VBA Code with InStr Function for Extension of Email Addresses

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.

=EXTENSION(B4)

Extension Formula with InStr in VBA Code

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.

Names of Some Employees

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

VBA Code for Extracting Shortnames with InStr 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:

=SHORTNAME(B4,-1)

Shortname Formula for First Names with InStr in VBA Code

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

=SHORTNAME(B4,1)

Shortname Formula for Last Names with InStr in VBA Code

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

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

ExcelDemy
Logo