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

Method 1 – Checking for Substrings

Open the Visual Basic Editor by pressing Alt + F11 or navigating to the Developer tab and clicking on Visual Basic.

In the code window, insert a new module (Insert -> Module).

Copy and paste the following code:

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

Run the macro.

If the primary string contains the specified substring (Hulk), you’ll see a Movie found message; otherwise, it will display Movie not found.

Method 2 – Checking for Numbers in Strings

In this example we will determine which strings contain numbers in the movie names.

Create a User Defined Function (UDF) to check if a string contains numbers.
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

Save the macro file (click the Save button).
Return to your worksheet and use the User Defined Function (e.g., SearchNumbers(Cell B5)). It will return TRUE if the string in the cell contains numbers, otherwise FALSE.

Drag the formula down using the Fill Handle to check other cells.

Method 3 – Extracting Numbers from Strings Using VBA

In the previous section, we learned how to check if a string contains numbers. Now let’s explore how to extract those numbers and place them in another cell using VBA. Follow the steps below:

Open the Visual Basic Editor by clicking on the Developer tab and selecting Visual Basic.
Insert a UserForm from the Insert tab in the code window.
From the Toolbox, drag and drop a CommandButton onto the UserForm.

Double click the button and paste the following code:

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.
Click the Command Button, and you will get the extracted numbers from the strings.

Method 4 – Checking for Specific Letters in a String Using VBA

This method is similar to checking for substrings in a string. Below is an example using the InStr function to find if a string contains a certain letter in Excel:

Open the Visual Basic Editor as before.
Insert a Module in the code window.
Copy and paste the following code:

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

Run the program. If your string contains the letter Z, you’ll see a Letter found message; otherwise, it will display Letter not found.

Method 5 – Checking if a Range of Strings Contains a Substring Using VBA

In the previous sections, we learned how to check whether individual strings contain specific substrings. Now let’s explore how to check if a range of strings contains a particular substring. Follow the steps below:

Open the Visual Basic Editor by clicking on the Developer tab and selecting Visual Basic.
Insert a new module in the code window.
Copy and paste the following code:

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

Run the code.

If any string within the specified range contains the substring Hulk, you’ll see a Movie found message; otherwise, it will display Movie not found.

Method 6 – Extracting Strings from a String Using VBA

In this section, we’ll focus on extracting specific substrings from a larger string. Let’s say we want to extract names starting with Chris from the following dataset. Follow these steps:

Open the Visual Basic Editor as before.
Insert a new module in the code window.
Copy and paste the following code:

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