Excel VBA to Match String in Column (5 Examples)

In Excel, we use both numeric data and string data. They may exist in the same column or the same cell. If we want to find cells with string data, we may do this manually. But the Excel VBA has some useful features by which we can identify those strings. We will discuss how to get a match string in a column using Excel VBA.

Excel VBA to Match String in Column: 5 Examples

We will try to get match strings in Excel using the following dataset.

Now, let’s see the VBA examples one by one.

1. A Simple VBA Code to Get the Number of Matches

With this VBA code, we will get how many times the specified string is found in the data range. The point to be noted is that we will set the matching string within the VBA code and change this criterion within the code each time we want. Now, let’s explore the VBA code example step by step.

Step 1:

• First, go to the Sheet Name field at the bottom of the sheet.
• Press the right button of the mouse. Choose View Code from the list.

The VBA command module will appear. We will write VBA code in this module.

Step 2:

• Now, go to the dataset and select the range B5:D9.
• Copy and paste the following VBA code into this module.

Sub Match_String_1()
Dim count_1 As Integer
count_1 = 0
For i = 1 To Selection.Rows.Count
    If Selection.Cells(i, 1) = "John" Then
        count_1 = count_1 + 1
    End If
Next i
MsgBox "Number of Matching found: " & count_1
End Sub

Step 3:

• Now, hit F5 to run the code.

We get 1 matching result from the 1st column of our selected range.

2. VBA Code with Range and Text Input to Count the Match

We will put the range and specific string directly on the code in this method.

Step 1:

• Press Alt+F11 to enter the command module.
• Write the following VBA code on the command module.

Sub Match_String_2()
Dim x As Long
Dim y, range_1 As Range
Set range_1 = Range("B5:B9")
With range_1
For Each y In .Rows
If Application.CountIf(y, "John") > 0 Then
x = x + 1
End If
Next
End With
MsgBox "Number of Matching found: " & x
End Sub

Step 2:

• Press F5 to run the VBAcode.

We get the match number from this method.

Read More: Excel VBA to Match Value in Range

3. Use VBA InputBox to Allow User to Input Reference String and Count the Match

This piece of VBA code will allow you to insert the reference string in an input box and count the number of matches each time you run the code.

Step 1:

• Hit Alt+F11 and enter the VBA command module.
• Copy the following VBA code and paste it into the module.

Sub Match_String_3()
Dim x As Long
Dim string_1 As String
Dim y, range_1 As Range
string_1 = InputBox("Enter Text")
Set range_1 = Application.InputBox("Please select a range", Type:=8)
With range_1
For Each y In .Rows
If Application.CountIf(y, string_1) > 0 Then
x = x + 1
End If
Next
End With
MsgBox "Number of Matching found: " & x
End Sub

Step 2:

• Press F5 to run the VBAcode.
• A window will appear to put a string to find a match.

Step 3:

• Press OK.
• Another window will appear to select a range. Select the range from the dataset.

Step 4:

• Again, press OK on that window.

We get the matching amount like the previous two methods.

4. Excel VBA to Get the Row Number Where the Matched String Is Located

We will find a match of a string in a column and get the row number.

Step 1:

• Press Alt+F11 to enter the command module.
• Put the following VBA code on the module.

Sub Match_String_4()
Dim match_quantity  As Variant
Dim string_1 As String
string_1 = InputBox("Enter Text")
match_quantity = WorksheetFunction.Match(string_1, Range("B5:B9"), 0)
MsgBox ("The match was found in row  " & match_quantity)
End Sub

Step 2:

• Press F5 to run the code.
• A new window will appear. Input a string on the marked box.

Step 3:

• Now, click OK to get the result.

We selected a range of B5:B9 and our match was found in the 5th row of that range.

5. Use of VBA InStr Function to Match String

In the following examples, we will use the VBA InStr function to match strings from a reference.

5.1 Find a Match of String with the Position

We will get the position of the string that matches in a string stream.

• Enter the command module by pressing Alt+F11.
• Paste the following VBA code here and run the code by pressing the F5 key.

Sub Match_String_5()
MsgBox InStr("Welcome to Microsoft Excel", "Welcome")
End Sub

This function is case-sensitive. We modified the reference string of the VBA code. Now, run the code to see the result.

Sub Match_String_5()
MsgBox InStr("Welcome to Microsoft Excel", "excel")
End Sub

We are getting “0” in the result, as we did not find any match.

5.2 Find Match of String with Position and Criteria

In this section, we will use criteria to get the position of a string.

• Hit Alt+F11 to enter the command module.
• Copy the VBA code and paste it into the module.
• Run the code by pressing the F5 key.

Sub Match_String_6()
MsgBox InStr(10, "What is Excel? Excel is a Microsoft Propety.", "Excel")
End Sub

We have Excel two times in the reference string stream. We skipped the 1st string by using a number that specified the position of the 1st string.

5.3 Find a Match of String from the Right Side and Get the Position

Usually, we count the position of a string from the left side. We can also count from the right side.

• Hit Alt+F11 and enter the VBA command module.
• Put the code below.
• Hit F5 to run the code.

Sub Match_String_7()
  MsgBox InStrRev("What is Excel? Excel is a Microsoft Property.", "Excel")
End Sub

We counted the position of the string Excel from the right side.

5.4 Show If a Match of String Is Found or Not

We will try to show the matching status in the result.

• Enter the VBA command module by pressing Alt+F11.
• Write the following VBA code on the command module and run the code by pressing F5.

Sub Match_String_8()
If InStr("Welcome to Microsoft Excel", "Excel") = 0 Then
   MsgBox "Match not found"
Else
   MsgBox "Minimum one match exist"
End If
End Sub

5.5 Show Position If a Match of String Is Found or Not

This method will show the position of the string if a match is found otherwise, indicating that not been found.

• Enter the VBA command module by pressing the Alt+F11 keys.
• Copy and paste the below VBA Then, run the code by pressing the F5 button.

Sub Match_String_9()
    Dim x As Long
    x = InStr("Welcome to Microsoft Excel", "Excel")
    If x = 0 Then
        MsgBox "Word not found"
    Else
        MsgBox "Word found in position: " & x
    End If
End Sub

5.6 Print the Match Status in a Newly Added Column

In this section, we will check the matching print status in the dataset.

• To enter the command module press Alt+F11.
• Now, write the VBA code on the module.

Sub Match_String_10()
Dim string_1 As Range
For Each string_1 In Range("C5:C9")
    If InStr(string_1.Value, "Accounts") > 0 Then
        string_1.Offset(0, 2).Value = "Yes"
        Else
        string_1.Offset(0, 2).Value = "No"
        End If
    Next string_1
End Sub

• Now, run the code by pressing F5.

We can see that if a match is found on the row then shows Yes, otherwise No.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Conclusion

In this article, we described how to find a match string in a column using Excel VBA. We also showed how to get the match from a string stream. I hope this will satisfy your needs. Please have a look at our website and give your suggestions in the comment box.