Learn about Excel VBA InStr
The InStr function in Excel VBA is used to find the position of one string within another string. It returns the position at which a substring first appears within another string, or 0 if the substring is not found. This is useful when you need to search within strings or check if a substring exists within a larger string.
InStr function finds the position of a specified substring within the string and returns the first position of its occurrence.
Syntax
InStr([start], string1, string2, [compare])
Parameters
- start (Optional): The position to start the search from. Default is 1.
- string1: The string to search within.
- string2: The substring to search for.
- compare (Optional): Specifies the type of comparison. Options are:
-
- vbBinaryCompare (0): Case-sensitive comparison.
- vbTextCompare (1): Case-insensitive comparison.
-
Returns
- The position (integer) of string2 in string1.
- Returns 0 if string2 is not found.
Examples
- Basic Case-Insensitive Search:
- Case-Sensitive Search:
- Search starting from a specific position:
Use Cases
- Check if a substring exist in a string:
- Find the position of the first occurrence of a character in a string
InStr is a powerful function for string manipulation tasks in VBA, especially useful for conditional checks and locating specific substrings.
Q & A
Q1. How can I check if a word exists in a string?
Answer: Use InStr to check if the word appears by testing if the result is greater than 0.
Q2. How do I perform a case-insensitive search?
Answer: Use vbTextCompare to make the search case-insensitive.
Q3. How can I find the position of the second occurrence of a word?
Answer: Start the search after the first occurrence by setting the start parameter to the position after the first find.
Q4. What does InStr return if the substring is not found?
Answer: InStr returns 0 if the substring is not found.
You can read on below topic for increase expertise
1.VBA Dim
2. VBA Loops
