Find position of a string in another string by using Instr function of VBA Excel

Find string using Instr vba excel

Objective

Find position of a string in another string by using Instr function of VBA Excel.

Approach

InStr function returns the position of a substring in another string, if it matches the substring in the string then it returns the position, while if it does not find any match then it will return zero.

 

Search a string in another string without optional parameters

If we do not mention the search position and compare argument in the instr function, then in that case by default search will start at 1st position and it will be a case sensitive search.

Code

Sub Search_String_Without_Optional_Parameter()

Dim StringToBeSearched As String
Dim MyString As String, StrPos As Double


StringToBeSearched = "VBA Excel is used for automation"
MyString = "automation"

'If we do not use optional parameters , then search is case sensitive and starts at 1st position
StrPos = InStr(StringToBeSearched, MyString)


End Sub

 

Find string using Instr vba excel

 

Case sensitive search with optional parameters

In the code below we have mentioned the search position and compare argument in the function parameters. Here the search will start at 1st position, since we have explicitly mentioned “1” as first parameter. We have used vbBinaryCompare as an argument in the function, vbBinaryCompare will apply case sensitive search.

Code

Sub Search_String_Case_Sensitive()

Dim StringToBeSearched As String
Dim MyString As String, StrPos As Double


StringToBeSearched = "VBA Excel is used for automation"
MyString = "Automation"

'Here the search is case sensitive , and serach started at 1st position
StrPos = InStr(1, StringToBeSearched, MyString, vbBinaryCompare)

End Sub

 

Find string using Instr vba excel

 

Non-case sensitive search with optional parameters

In the below case we have used vbTextCompare, vbTextCompare makes the search Non case sensitive, which means “automation” and “Automation” will be treated as same. Here search will start from the 1st position.

Code

Sub Search_String_Non_Case_Sensitive()

Dim StringToBeSearched As String
Dim MyString As String, StrPos As Double


StringToBeSearched = "VBA Excel is used for automation"
MyString = "Automation"

'Here the search is Non case sensitive , and serach started at 1st position
StrPos = InStr(1, StringToBeSearched, MyString, vbTextCompare)

End Sub

 

Find string using Instr vba excel

 

Search string in another string from any position

In the code below we have started the search from 5th position, as we can see that there were 2 strings called “automation” in the variable StringToBeSearched, but since we had explicitly mentioned 5 as starting position, it will ignore the 1st appearance of string “automation”. Here we have used the vbTextCompare option, which makes the search non case sensitive.

Sub Search_String_From_Changed_Position()

Dim StringToBeSearched As String
Dim MyString As String, StrPos As Double

StringToBeSearched = "automation plus automation"
MyString = "Automation"

'Here search is starting at 5th position, so we are ignoring the first string i.e. "automation"
StrPos = InStr(5, StringToBeSearched, MyString, vbTextCompare)
End Sub

 

Find string using Instr vba excel

 

Reference

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/instr-function

Post you may like

Identify duplicate values in column and highlight them in vba excel