Search
Close this search box.
"FAT-Coder Web development, data analysis, app development"
Courses
  • ECEL TUTORIAL

VBA – IntStr String

Previous
Next

The instr function is designed to find the position of the first occurrence of a specified substring (string2) within a given string (string1).

  • string1: The main string where you want to search for the substring.
  • string2: The substring you are searching for within string1.


Return Value:

  • Integer:
    • Returns the position (index) of the first character of string2 within string1.
    • If string2 is not found within string1, it returns 0.
    • If string2 is an empty string (“”), it returns 1.

Syntax:

Instr([start], string1, string2[, compare])

Parameter:

 

  • start: (Optional) The position within string1 to start the search. If omitted, the search begins at the first character.
  • string1: The string within which you want to search for string2.
  • string2: The string that you want to find within string1.
  • compare: (Optional) Specifies the comparison method:
    • vbBinary: Performs a binary comparison (case-sensitive).
    • vbTextCompare: Performs a text comparison (case-insensitive).
    • vbDatabaseCompare: Uses the database’s collation settings for comparison.
    • 0: Equivalent to vbBinary.
    • 1: Equivalent to vbTextCompare.

 

Example

				
					Dim str1 As String
Dim str2 As String
Dim pos As Integer

str1 = "Hello, World!"
str2 = "World"

pos = Instr(1, str1, str2) 

' pos will be 7 

pos = Instr(1, str1, "world") ' Case-sensitive, will return 0

pos = Instr(1, str1, "world", vbTextCompare) ' Case-insensitive, will return 7

Use Cases:

  • Text Manipulation:
    • Finding specific words or phrases within text strings.
    • Extracting substrings from larger strings.
    • Data validation and cleaning.
  • String Comparisons:
    • Checking if a string contains a specific substring.
    • Finding the position of a character or symbol within a string.
Previous
Next