How to use Like Operator in Excel VBA?

In this post, you’ll learn how to use Like operator with-in your excel spreadsheet using Excel VBA.

Like Operator in Excel VBA

Like operator is used to compare two strings. It can be used to check the matching of the stings.

Syntax:

If variable name Like “required match” Then
     Statements
 Else
     Statements
 End If

Sample program Using the Like Operator to Compare Two Strings

Code:

Sub UsingLikeOperator():

Dim Name As String

Dim Result As Boolean

Name = "Mr. Alpha"

If Name Like "Mr*" Then

    Result = True

Else

    Result = False

End If

MsgBox Result

End Sub

Using the Like Operator with Different Matching Patterns

Using the Like operator, you can check the matching of two strings based on different patterns.

Here is the list of possible matching patterns:

• * – Matches 0 or more characters
• ?  – Matches a single character
• #  – Matches a single digit
• [chars]  – Matches a single character from a char list.
• [A-Z]  – This matches any uppercase character from the alphabet.
• [A-Za-z]  – Matches any character from the alphabet.
• [!chars]   – This Matches a single character excluding a char list.

 Let us see each matching pattern with an example.

Matching a single character

Code:

Text = "ABCDE"

  If Text Like "AB?DE" Then

    Result = True

Else

    Result = False

End If

Matching a single digit

Code:

Text = "AB7DE"

If Text Like "AB#DE" Then

    Result = True

Else

    Result = False

End If

Matching any uppercase character from the alphabet

Code:

Text = "ABCDE"

If Text Like "AB[A-Z]DE" Then

    Result = True

Else

    Result = False

End If

Not matching any uppercase character from the alphabet

Code:

Text = "AB7DE"

If Text Like "AB[!A-Z]DE" Then

    Result = True

Else

    Result = False

End If

Matching any character from the alphabet (uppercase or lowercase)

Code:

Text = "ABcDE"

If Text Like "AB[A-Za-z]DE" Then

    Result = True

Else

    Result = False

End If