Like Operator

Description

Used to compare two strings.

Syntax

result = string Like pattern

The Like operator syntax has these parts:

Remarks

If string matches pattern, result is True; if there is no match, result is False; and if either string or pattern is a Null, result is also a Null.

The behavior of the Like operator depends on the Option Compare statement. Unless otherwise specified, the default string-comparison method for each module is Option Compare Binary.

Option Compare Binary results in string comparisons based on a sort order derived from the internal binary representations of the characters. In Microsoft Windows, sort order is determined by the code page. On the Macintosh, sort order is determined by the character set. In the following example, a typical binary sort order is shown:

A < B < E < Z < a < b < e < z < À < Ê < Ø < à < ê < ø

Option Compare Text results in string comparisons based on a case-insensitive textual sort order determined by your system's locale. The same characters shown above, when sorted using Option Compare Text, produce the following text sort order:

(A=a) < ( À=à) < (B=b) < (E=e) < (Ê=ê) < (Z=z) < (Ø=ø)

Built-in pattern matching provides a versatile tool for string comparisons. The pattern-matching features allow you to use wildcard characters, character lists, or character ranges, in any combination, to match strings. The following table shows the characters allowed in pattern and what they match:

A group of one or more characters (charlist) enclosed in brackets ([ ]) can be used to match any single character in string and can include almost any character code, including digits.

Note

The special characters left bracket ([), question mark (?), number sign (#), and asterisk (*) can be used to match themselves directly only by enclosing them in brackets. The right bracket (]) can't be used within a group to match itself, but it can be used outside a group as an individual character.

In addition to a simple list of characters enclosed in brackets, charlist can specify a range of characters by using a hyphen (-) to separate the upper and lower bounds of the range. For example, [A-Z] in pattern results in a match if the corresponding character position in string contains any of the uppercase letters in the range A through Z. Multiple ranges are included within the brackets without any delimiters.

The meaning of a specified range depends on the character ordering valid at run time (as determined by Option Compare and the locale setting of the system the code is running on). Using the same example shown above with Option Compare Binary, the range [A-E] matches A, B and E. With Option Compare Text, [A-E] matches A, a, À, à, B, b, E, e. Note that it does not match Ê or ê because accented characters fall after unaccented characters in the sort order.

Other important rules for pattern matching include the following:

• An exclamation point (!) at the beginning of charlist means that a match is made if any character except the ones in charlist are found in string. When used outside brackets, the exclamation point matches itself.
• The hyphen (-) can appear either at the beginning (after an exclamation mark if one is used) or at the end of charlist to match itself. In any other location, the hyphen is used to identify a range of characters.
• When a range of characters is specified, they must appear in ascending sort order (from lowest to highest). [A-Z] is a valid pattern, but [Z-A] is not.
• The character sequence [] is ignored; it is considered a zero-length string.

See Also

Comparison Operators, InStr Function, Operator Precedence, Option Compare Statement, StrComp Function.

Example

This example uses the Like operator to compare a string to a pattern.

MyCheck = "aBBBa" Like "a*a"    ' Returns True.
MyCheck = "F" Like "[A-Z]"    ' Returns True.
MyCheck = "F" Like "[!A-Z]"    ' Returns False.
MyCheck = "a2a" Like "a#a"    ' Returns True.
MyCheck = "aM5b" Like "a[L-P]#[!c-e]"    ' Returns True.
MyCheck = "BAT123khg" Like "B?T*"    ' Returns True.
MyCheck = "CAT123khg" Like "B?T*"    ' Returns False.

This example deletes every defined name that contains "temp" in the name. The Option Compare Text statement must be included at the top of any module that contains this example.

For Each nm In ActiveWorkbook.Names
    If nm.Name Like "*temp*" Then
        nm.Delete
    End If
Next nm

This example adds an arrowhead to every graphic object on Sheet1 that has the word "Line" in its name.

For Each d In Worksheets("Sheet1").DrawingObjects
    If d.Name Like "*Line*" Then
        d.ArrowHeadLength = xlLong
        d.ArrowHeadStyle = xlOpen
        d.ArrowHeadWidth = xlNarrow
    End If
Next