How to use wildcard characters in VBA Excel

wildcard character vba excel

Objective

To use like operator with wild card characters in VBA Excel.

Approach

We use wild card characters to match a particular pattern, we use Like operator with wild card characters to match it in a string.For example, if we use “S*” with like operator, then we are searching for a string which starts with a capital “S” and there can be multiple characters after “S”.Pattern matching with wild card characters is case sensitive.

 

Use  “*” (asterisk) to match pattern

In the code below , we are trying to match the strings which starts with “Test” and then there can be any number of characters after “Test”.In the code below we loop though each cell value in column A. As a result we find that “Test_1” and “Test_2” match this criterion because only these 2 strings start with “Test”.Once the criterion is matched we highlight the cells in yellow.

 

Sub WildCard_1()
Dim LastRow As Double
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For j = 1 To LastRow
If Range("A" & j).Value Like "Test*" Then
Range("A" & j).Interior.Color = RGB(255, 255, 0)
End If
Next j
End Sub

 

wildcard character vba excel

 

Use  “?”  to match pattern

In the below code, we are looking for a string which starts with “T” , and after that there is a single character which falls between “e” to “g” and in the last position there can be any character.The length of such a string will be 3. Only string “Tea” matches this pattern as shown in the image below.We are highlighting this cell in yellow.

 

Sub WildCard_3()
Dim LastRow As Double

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

For j = 1 To LastRow
If Range("A" & j).Value Like "T[e-g]?" Then
Range("A" & j).Interior.Color = RGB(255, 255, 0)
End If
Next j
End Sub

 

wildcard character vba excel

 

Use Wild Card character “#”

We use “#” character to match a single digit, which means if we use “#” within a string , it means we are trying to match it with single digit, like 1,2,3 etc. We can match between 0 to 9.

In the below code we are matching pattern for “Test#” , which means we are looking for strings like “Test1”, “Test2” or “Test9” etc.In simple terms we are looking for a string which starts with “Test” and after that there is a single digit.

Sub WildCard_3()
Dim LastRow As Double
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For j = 1 To LastRow
If Range("A" & j).Value Like "Test#" Then
Range("A" & j).Interior.Color = RGB(255, 255, 0)
End If
Next j
End Sub
Next j
End Sub

 

wildcard character vba excel

 

Use [ charlist ] to match pattern

It is a character list which is used as a wild card.In this we match a string with a list of characters. For example “T[a-e]g” will match with any string which starts with “T” and 2nd  character can be from range {a ,b, c,d,e} and last character will be “g”.So only string “Tag” matches the criterion because it starts with “T”, second character is “a” , which is from character list {a ,b, c,d,e}  and last character is “g”.

 

Sub WildCard_4()
Dim LastRow As Double
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For j = 1 To LastRow
If Range("A" & j).Value Like "T[a-e]g" Then
Range("A" & j).Interior.Color = RGB(255, 255, 0)
End If
Next j
End Sub

 

wildcard character vba excel

 

Use [ !charlist ] to match pattern

Here we check that a character should not match any character which is found in character list. For example “T[!a-e]g” will match with “Tug” , but it will not match with “Tag”. It matches with “Tug” because 2nd character “u” does not fall in the character list {a,b,c,d,e,f,g}. Exclamatory sign “!” specifies that it will exclude the characters which are found in the character list.

 

Sub WildCard_5()
Dim LastRow As Double
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For j = 1 To LastRow
If Range("A" & j).Value Like "T[!a-e]g" Then
Range("A" & j).Interior.Color = RGB(255, 255, 0)
End If
Next j
End Sub

 

wildcard character vba excel

 

Post you may like

Delete charts from PowerPoint Slide using VBA Excel

Reference

https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/operators/like-operator
https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/wildcard-characters-used-in-string-comparisons