VBA : How to Use Wildcards

This tutorial explains how to use wildcards in VBA, along with examples.

Wildcard characters are used to represent one or more characters in a formula. They are useful when data is incomplete or only partially available.

You can download the following dataset to practice.

What are Wildcards

In this section, we will use different wildcards and their usage.

Asterisk (*)

It represents any number of characters (including zero characters as well). Let's understand this by considering a few examples.

A* : It means 'A' followed by any number of characters. For example- Apple, Antarctica, A1546, A.
*ed : It means any number of characters followed by 'ed'. For example- Coded, Removed, Deleted, Advanced.
A*Z : It means any text that starts with 'A' and ends with 'Z'. For example- AeZ, Abz, AXZ.

Let's take an example to understand how the asterisk works. The following code finds all the words that begin with letter 'D' in the dataset.

Sub startwithD()
    Dim cell As Range
    Dim ws As Worksheet
        Set ws = ThisWorkbook.Sheets("Sheet1")
        For Each cell In ws.Range("A1:A5")
    
        If cell.Value Like "D*" Then
            cell.Interior.Color = vbRed
        End If
    Next cell
End Sub

Press Run or F5 to run the above code.

Question Mark (?)

It represents exactly one character. Let's take a few examples to get a better understanding.

A?B : It means any string that starts with 'A' has exactly one character in the middle and ends with 'B'.

The following code finds all the word in a dataset that contain only a single character between 'D' and 'v'.

Sub D_v()
    Dim cell As Range
    Dim ws As Worksheet
        Set ws = ThisWorkbook.Sheets("Sheet1")
        For Each cell In ws.Range("A1:A5")
    
        If cell.Value Like "D?v" Then
            cell.Interior.Color = vbRed
        End If
    Next cell
End Sub

Press Run or F5 to run the above code.

Wildcards can be used with Excel formulas, LIKE Operator and the Find Method. Let's take some practical examples to understand how to use wildcards.

1. Using Wildcards with Find Method

We can use the Find method with wildcards to find the position of a value that matches a search pattern. The following code finds the position of value that starts with 'D' and ends with 'T' in column B of the dataset.

Sub MatchWithFindAndWildcards()
    Dim matchResult As Range
    Dim searchPattern As String
    Dim lastRow As Long
    Dim ws As Worksheet
    
    Set ws = ThisWorkbook.Sheets("Sheet2")
    
    searchPattern = "D*T"
    
    lastRow = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row
    
    Set matchResult = ws.Range("B1:B" & lastRow).Find(What:=searchPattern, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)
    
    If Not IsError(matchResult) Then
        ws.Cells(lastRow + 1, 2).Value = "Match found at row " & matchResult.Row
    Else
        ws.Cells(lastRow + 1, 2).Value = "NO match found"
    End If
End Sub

Press Run or F5 to run the above code.

2. Using Wildcards with COUNTIF

We can use the COUNTIF function with wildcards to count the number of cells that match a specified pattern. The following code counts the number of entries that begin with 'J' in column A of the dataset.

Sub CountIfWithWildcards()
    Dim countResult As Long
    Dim searchPattern As String
    Dim lastRow As Long
    Dim ws As Worksheet
    
    Set ws = ThisWorkbook.Sheets("Sheet2")
    
    searchPattern = "J*"
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    On Error Resume Next
    countResult = Application.WorksheetFunction.CountIf(ws.Range("A1:A" & lastRow), searchPattern)
    On Error GoTo 0
  
    ws.Range("A" & lastRow+1).Value = countResult
End Sub

Press Run or F5 to run the above code.

3. Using Wildcards with SUMIF

We can use the SUMIF function with wildcards to sum the values in cells that match a specified pattern. The following code finds the sum of the salaries corresponding to entries ending with 'er' in column B.

Sub SumIfWithWildcards()
    Dim sumResult As Double
    Dim searchPattern As String
    Dim lastRow As Long
    Dim ws As Worksheet
    
    Set ws = ThisWorkbook.Sheets("Sheet2")
    searchPattern = "*er"
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    On Error Resume Next
    sumResult = Application.WorksheetFunction.SumIf(Range("B1:B" & lastRow), searchPattern, Range("C1:C" & lastRow))
    On Error GoTo 0
    
    ws.Range("C" & lastRow + 1).Value = sumResult
    
End Sub

Press Run or F5 to run the above code.

About Author:
Yash Gaur

Yash is pursuing an MBA in Finance with a keen interest in analytics. He enjoys working with data and leveraging his research and analytical skills to generate valuable insights.

Let's Get Connected Email LinkedIn