VBA to Check If String Contains Another String in Excel (6 Methods)

Implementing VBA is the most effective, quickest and safest method to run any operation in Excel. In this article, we will show you how to check if a string contains another string in Excel using VBA.

Download Practice Template

You can download the free practice Excel template from here.

6 Methods in VBA to Check If String Contains Another String in Excel

Below in this section, you will find 6 effective methods on how to implement VBA to check if a string contains another string or not.

1. VBA to Check If String Contains Substring

Below is an example of the InStr function to find if a string contains a substring in Excel.

Steps:

• Press Alt + F11 on your keyboard or go to the tab Developer -> Visual Basic to open Visual Basic Editor.

• In the pop-up code window, from the menu bar, click Insert -> Module.

• In the code window, copy the following code and paste it.

Public Sub ContainSub()
If InStr("Movie: Iron Man, Batman, Superman, Spiderman, Thor", "Hulk") > 0 Then
MsgBox "Movie found"
Else
MsgBox "Movie not found"
End If
End Sub

Your code is now ready to run.

• Run the macro.

If your string contains the substring then you will get a match found, otherwise, it will return no match found.

In our example, we wanted to find out whether our primary string “Movie: Iron Man, Batman, Superman, Spiderman, Thor” contains the word “Hulk” or not. As it does not, we get a Movie not found result.

Read More: Length of a String with VBA in Excel (With Macro and UDF)

2. VBA to Check If String Contains Number

You can search whether strings contain numbers or not by using the VBA code.

Look at the following example where we will find which strings are containing numbers with the movie names.

Steps to check if strings contain numbers with VBA are given below.

Steps:

• Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• In the code window, copy the following code and paste it.

Function SearchNumbers(oRng As Range) As Boolean
     Dim bSearchNumbers As Boolean, i As Long
     bSearchNumbers = False
     For i = 1 To Len(oRng.Text)
         If IsNumeric(Mid(oRng.Text, i, 1)) Then
             bSearchNumbers = True
             Exit For
         End If
     Next
     SearchNumbers = bSearchNumbers
End Function

• This is not a Sub Procedure for the VBA program to run, this is creating a User Defined Function (UDF), which we will call in our worksheet to execute the task. So, after writing the code, instead of clicking the Run button, click on the Save button from the menu bar to save the macro file.
• Now go back to the worksheet of interest and write the user-defined function that you just created in the VBA code (SearchNumber, in the first line of the code) and inside the brackets of the function, input the cell reference number of the cell of string that has leading numbers (e.g. Cell B5).
• Press Enter.

You will get a boolean value (TRUE or False), if the string in the cell contains numbers then you will get TRUE, otherwise FALSE.

• Drag the cell down by Fill Handle to apply the formula to the rest of the cells to check which string contains numbers and which doesn’t.

3. VBA to Extract Numbers from String

In the above section, we learn how to check if the string contains numbers or not. And in this section, we will learn how to extract those numbers and place them in another cell with the example given below.

Steps to check if strings contain numbers and extract them with VBA are given below.

Steps:

• Open Visual Basic Editor from the Developer tab and Insert a UserForm this time from the Insert tab in the code window.
• From the appeared Toolbox, drag and drop CommandButton in the UserForm.

• Double click on the button, copy the following code and paste it.

Private Sub CommandButton1_Click()
Worksheets("Number").Range("C2:C15").ClearContents
    checkNumber (Worksheets("Number").Range("B2:B15"))
End Sub
Sub checkNumber(objRange As Range)
    Dim myAccessary As Variant
    Dim i As Long
    Dim iRow As Long
    iRow = 2
    For Each myAccessary In objRange
        For i = 1 To Len(myAccessary.Value)
            If IsNumeric(Mid(myAccessary.Value, i, 1)) Then
                If Trim(objRange.Cells(objRange.Row - 1, 2)) <> "" Then
                    objRange.Cells(iRow - 1, 2) = _
                        objRange.Cells(iRow - 1, 2) & Mid(myAccessary.Text, i, 1)
                Else
                    objRange.Cells(iRow - 1, 2) = Mid(myAccessary.Text, i, 1)
                End If
            End If
        Next i
        iRow = iRow + 1
    Next myAccessary
End Sub

• Run the code and it will take you to the worksheet of interest.
• Click the Command Button and you will get the extracted number from the strings.

4. VBA to Check If String Contains Certain Letter

This method is almost similar to the method of checking substring in a string.

Below is the InStr function to find if a string contains a certain letter in Excel.

Steps:

• Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• In the code window, copy the following code and paste it.

Public Sub ContainChar()
If InStr("Movie: Iron Man, Batman, Superman, Spiderman, Thor", "Z") > 0 Then
MsgBox "Letter found"
Else
MsgBox "Letter not found"
End If
End Sub

Your code is now ready to run.

• Run the program. If your string contains the letter then you will get a match found, otherwise, it will return no match found.

In our example, we wanted to find out whether our primary string “Movie: Iron Man, Batman, Superman, Spiderman, Thor” contains the letter “Z” or not. As it does not, we get a Letter not found result.

5. VBA to Check If a Range of String Contains Another String

We have learned how to check whether a given string contains another string or not. But in this section, we will learn how to check if a range of strings contains substring or not with the following dataset as an example.

Steps:

• Open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• In the code window, copy the following code and paste it.

Public Sub ContainsSub()
If InStr(ActiveSheet.Select, "Hulk") > 0 Then
MsgBox "Movie found"
Else
MsgBox "Movie not found"
End If
End Sub

Your code is now ready to run.

• Run the code.

If your range of string contains the substring then you will get a match found, otherwise, it will return no match found.

6. VBA to Extract Strings from String

In this section, we will see how to check if strings contain certain substrings and extract those in another cell.

We will extract the information of the names starting with “Chris” from the following dataset.

Steps:

• Open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• In the code window, copy the following code and paste it.

Sub SearchSub()
Dim lastrow As Long
Dim i As Integer, count As Integer
lastrow = ActiveSheet.Range("A30000").End(xlUp).Row
For i = 1 To lastrow
    If InStr(1, LCase(Range("C" & i)), "Chris") <> 0 Then
        count = count + 1
        Range("F" & count & ":H" & count) = Range("B" & i & ":D" & i).Value
    End If
Next i
End Sub

Your code is now ready to run.

• Run the code.

Only the names starting with “Chris” will be stored in the predefined cells.

Conclusion

This article showed you how to check if a string contains certain strings in Excel using VBA macro. I hope this article has been very beneficial to you. Feel free to ask any questions regarding the topic.