« Column Letters To Numbers
The Planning Deskbook »

Simulating a LIKE function

7th June 2004, 10:42 am by Dick Kusleika

Excel doesn’t have a LIKE function, but you can use the COUNTIF function to pretend that it does. If LIKE existed, you might test the pattern of a cell like this

=LIKE(A1,”DD???F*”)

to return True for an entry in A1 like “DD123F6″. The same syntax can be used for COUNTIF

=COUNTIF(A1,”DD???F*”)

to return 1 if the pattern matches and 0 if it doesn’t. By limiting the range argument of COUNTIF to one cell, it acts somewhat like a LIKE function would.

Using COUNTIF, you don’t have the range of patterns that are available to you with the Like operator in VBA. You basically just have the * and ? wildcard characters. But it’s better than nothing.

Category: Worksheet Functions  |  Comment (RSS)

4 Comments

  1. J-Walk:

    And if you don’t mind a little VBA, this function simulates Like exactly:

    Function ISLIKE(arg, pattern As String) As Boolean
    ‘ Returns true if the first argument is like the second
    ISLIKE = arg Like pattern
    End Function

    Example:

    =ISLIKE(UPPER(A1),UPPER(”*[aeiou]*”))

    Returns TRUE if cell A1 contains a vowel (upper or lower case)

    7 June 2004, 11:55 am

  2. tuxedobuford:

    Hi, I’d like to use a cell reference instead of a defined string but can’t seem to get the syntax correct when combining the wildcard with the cell reference. Any ideas?

    Thanks.

    6 March 2008, 10:13 am

  3. tuxedobuford:

    Found it..COUNTIF(RANGE,”*” & CELL & “*”)
    whereas RANGE is my search range and CELL contains the string I’m counting.

    Thanks.

    6 March 2008, 12:09 pm

  4. Andrea:

    Thanks. I’ve been trying to figure out how to do this in Excel for an eternity.

    5 September 2008, 10:33 am

Certain comments are subject to moderation and may not appear immediately. You can use HTML tags in your comment. If you include a greater-than or less-than sign or anything else that could be interpreted as HTML, your comment won't look nice. You need to escape those characters. To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].

Leave a comment