Microsoft Office Tutorials and References

In Depth Information

[
list
] Matches any single character in the list

[!list] Matches any single character not in the list

The following formula returns TRUE because the question mark (?) matches any single character. If the first ar-

gument were “Unit12”, the function would return FALSE.

=ISLIKE(“Unit1”,”Unit?”)

The function also works with values. The following formula, for example, returns TRUE if cell A1 contains a

value that begins with 1 and has exactly three numeric digits:

=ISLIKE(A1,”1##”)

The following formula returns TRUE because the first argument is a single character contained in the list of

characters specified in the second argument:

=ISLIKE(“a”,”[aeiou]”)

If the character list begins with an exclamation point (!), the comparison is made with characters
not
in the list.

For example, the following formula returns TRUE because the first argument is a single character that does not

appear in the second argument's list:

=ISLIKE(“g”,”[!aeiou]”)

To match one of the special characters from the previous table, put that character in brackets. This formula re-

turns TRUE because the pattern is looking for three consecutive question marks. The question marks in the pat-

tern are in brackets so they no longer represent any single character:

=ISLIKE(“???”,”[?][?][?]”)

The Like operator is very versatile. For complete information about the VBA Like operator, consult the Help

system.

Does a cell contain a particular word?

What if you need to determine whether a particular word is contained in a string? Excel's FIND function can de-

termine whether a text string is contained in another text string. For example, the formula that follows returns
5,

the character position of
rate
in the string
The rate has changed:

=FIND(“rate”,”The rate has changed”)

The following formula also returns
5:

=FIND(“rat”,”The rate has changed”)

However, Excel provides no way to determine whether a particular word is contained in a string. Here's a VBA

function that returns TRUE if the second argument is contained in the first argument: