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.
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? First, this creates a function called DECISION with a string argument named string1. It declares an integer variable named Position. 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 “@”. 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). 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
- 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.
=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.
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.
=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.
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
- How to Use VBA Forecast Function in Excel (3 Ideal Examples)
- Call a Sub in VBA in Excel (4 Examples)
- How to Create a Body Mass Index Calculator in Excel Using VBA
- Use VBA Asc Function (5 Practical Examples)
- How to Use VBA Large Function in Excel (4 Suitable Examples)
- Use VBA StrComp in Excel (5 Common Examples)
- How to Use IsNull Function in Excel VBA (5 Examples)