The VBA InStr function is used to search for a specific character or string within a larger string. It returns an integer value that indicates the starting position of the first occurrence of the search criteria.
VBA InStr Function – Purpose, Syntax and Arguments
Purpose:
The purpose of the InStr function is to allow for efficient string manipulation and searching within VBA code. It can be used to find and extract specific data from strings or to check for the existence of certain characters or strings within a larger string.
Syntax:
InStr([start], string, substring, [compare])
- Start: This is an optional argument that specifies the starting position of the search. If not specified, the default value is 1 (the beginning of the string).
- String: This is the string in which the search will be performed.
- Substring: This is the character or string to be searched for in the ‘string’ argument.
- Compare: This is an optional argument that specifies the type of comparison to be performed. It can take the following values:
- 0 – Binary comparison (default)
- 1 – Textual comparison
- 2 – Case-insensitive comparison
Example:
To demonstrate the use of the InStr function, let’s consider a scenario where we have a list of employees in the format “Last Name, First Name” and we need to find and extract the last name from each employee’s name.
Sub ExtractLastName() Dim employeeName As String Dim commaPosition As Integer employeeName = "Smith, John" commaPosition = InStr(employeeName, ",") 'commaPosition will return the value 6, as the comma is located at the 6th position in the string "Smith, John" Dim lastName As String lastName = Left(employeeName, commaPosition - 1) 'Using the 'Left' function, we can extract the characters before the comma to get the last name MsgBox "Last name is: " & lastName 'This will display a message box with the text "Last name is: Smith" End Sub
Remarks:
- The InStr function is case-sensitive by default. This means that “John” and “john” will be considered as two different substrings. To perform a case-insensitive search, the ‘compare’ argument needs to be specified as 1 or 2.
- When the ‘substring’ argument is not found within the ‘string’, the InStr function will return the value 0.
- If the ‘substring’ argument is an empty string (“”) or a null value, the InStr function will return the value 1.
Important Notes:
- The InStr function is a very useful tool for manipulating and searching strings in VBA. It can be combined with other string functions, such as ‘Left’, ‘Right’ and ‘Mid’, to perform more complex operations.
- In some cases, the ‘InStrRev’ function may be more suitable for a specific task, as it searches for a substring from the end of the string to the beginning.
- When working with large strings or performing multiple searches within the same string, the InStr function may significantly slow down the execution of the code. In such cases, it is recommended to use the InStr function within a loop and then exit the loop as soon as the desired result is found.
Understanding VBA InStr Function with Examples
Example 1: Basic Syntax
InStr([start], string, substring, [compare])
Description: The InStr function is used to find the position of a substring within a given string. It returns the position as a numeric value.
Code:
=InStr(1, "Hello World", "World")
Explanation: In this example, the InStr function searches for the substring “World” within the string “Hello World” starting at the first character. Since the substring is found in the string, it returns the numeric position of the first character of the substring, which is 7.
Example 2: Using a Variable for the Start Position
start = 3
InStr(start, string, substring)
Description: The start position in the InStr function can also be set using a variable. This is useful when the starting position needs to be determined dynamically.
Code:
start = 3 =InStr(start, "Hello World", "o")
Explanation: In this example, the start position is set to 3 using the variable “start”. The InStr function then searches for the substring “o” within the string “Hello World” starting at the third character. Since the substring is found at the 5th position, the function returns 5.
Example 3: Using the Compare Parameter
=InStr(1, “Hello WORLD”, “WORLD”, 0)
Description: The compare parameter in the InStr function allows the user to specify the type of comparison to be performed. By default, it performs a case-sensitive search, but it can be changed to a case-insensitive search.
Code:
=InStr(1, "Hello WORLD", "WORLD", 0)
Explanation: In this example, the compare parameter is set to 0, indicating a case-insensitive search. This means that the InStr function will search for the substring “WORLD” regardless of the case of letters in the string. As a result, it returns the position of the first character of the substring, which is 7.
Example 4: Using the Optional Start and Compare Parameters
InStr(string, substring, [start], [compare])
Description: The InStr function allows the start and compare parameters to be optional. If not specified, the function will use the default values, which are 1 for start and a case-sensitive search for compare.
Code:
=InStr("Hello World", "o")
Explanation: In this example, only the string and substring parameters are specified. Therefore, the function will search for the substring “o” within the string “Hello World” starting at the first character using a case-sensitive search. As a result, it returns 5, the position of the first character of the substring.
Example 5: Finding Multiple Occurrences of a Substring
Do While InStr(start, string, substring) > 0
Description: The InStr function can be used in a loop to find all occurrences of a substring within a string. The loop will continue until the InStr function returns 0, indicating that there are no more occurrences of the substring.
Code:
start = 1
Do While InStr(start, "Hello World", "o") > 0
start = InStr(start, "Hello World", "o") + 1
MsgBox "Substring found at position: " & start
Loop
Explanation: In this example, the InStr function is used in a loop to find all occurrences of the substring “o” within the string “Hello World”. The loop starts at the first character and continues until there are no more occurrences of the substring. For each occurrence, the position is displayed in a message box. The start position is updated at the end of each loop to continue searching for the next occurrence.
Example 6: Searching for a Substring in a Range of Cells
InStr(1, Range(“A1:A10”), “Hello”)
Description: The InStr function can also be used to search for a substring within a range of cells in a worksheet. The function will return the position of the first occurrence of the substring within the range.
Code:
=InStr(1, Range("A1:A10"), "Hello")
Explanation: In this example, the InStr function searches for the substring “Hello” within the range A1:A10 in the worksheet. It returns the position of the first cell in the range that contains the substring.
Example 7: Using Wildcards in the Substring Parameter
=InStr(1, “Hello World”, “H*llo”)
Description: The InStr function allows the use of wildcards in the substring parameter. This is helpful when the exact substring is not known, but a pattern or partial string is known.
Code:
=InStr(1, "Hello World", "H*llo")
Explanation: In this example, the substring parameter contains a wildcard (*) which means the function will search for any string that starts with an “H” and ends with “llo” within the string “Hello World”. As a result, it returns the position of the first occurrence of “Hello” which is 1.
Example 8: Using Regular Expressions in the Substring Parameter
=InStr(1, “Hello World”, “[A-Z]*llo”)
Description: The InStr function can also use regular expressions in the substring parameter. This feature allows for more advanced and flexible searches based on specific patterns.
Code:
=InStr(1, "Hello World", "[A-Z]*llo")
Explanation: In this example, the substring parameter contains a regular expression [A-Z]*llo which means the function will search for any string that starts with any capital letter and ends with “llo”. As a result, it returns the position of the first occurrence of “Hello” which is 1.
Conclusion:
The InStr function is a powerful tool for searching for a substring within a given string. It offers various parameters and options for customizing the search criteria and is versatile in its use with cells in a worksheet or string variables in VBA code. With the examples provided, one can grasp the basic and advanced concepts of the function and use it effectively in VBA programming.
