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
=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.
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.
=DECISION(B4
)
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”.
Similar Readings:
- How to Use VBA UCASE function in Excel (4 Examples)
- Use MsgBox Function in Excel VBA (A Complete Guideline)
- Use LCase Function in VBA in Excel (With 4 Examples)
- How to use VBA SPLIT function in Excel (5 Examples)
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.
=EXTENSION(B4)
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:
=SHORTNAME(B4,-1)
And to extract out the last names, the formula will be:
=SHORTNAME(B4,1)
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.