Simulating a LIKE function

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.

Posted in Uncategorized

12 thoughts on “Simulating a LIKE function

  1. 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)

  2. 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.

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

    Thanks.

  4. Hi, Mike

    For a worksheet formula, something like
    =isnumber(match(check_item,list_range,0))

    (Of course IN is available in SQL within Excel.)

    regards, Pete

  5. In Excel 2010, I have the formula below that works as long as the the data is an exact match but with the names in columns G and X there are often trailing spaces or misspellings. Is there a way to use a Like or wildcard function to pull something like “MC CH*” that would give me anything starting with MC CH??

    =SUMPRODUCT(SUBTOTAL(3,OFFSET(A34:A6000,ROW(A34:A6000)-ROW(A$34),0,1)),–(G34:G6000= “McArthur Jr., Melvin”),–(T34:T6000=3),–(X34:X6000=”MC CHESNEY “),–(N34:N6000=”OPEN”))

  6. Camille – you might want to check out Excels DSUM function. Excel has a whole bunch of Database functions called DSUM, DMAX, etc that filter a table based on criteria, and return an aggregated total. You can do very complex filtering indeed including non-additive filtering, advanced formulas within the criteria etc.

    You might also know that you can use Excel’s “datatables” functionality to ‘swap’ up to 2 inputs of a formula with a designated range of values, so you can see what difference a change in those two input parameters makes to your model output.

    I didn’t think that either function on their own was that much of a deal until I stumbled across this great article at http://support.microsoft.com/kb/282851 about combining them. I had not realised until I read the article that you can use datatables to ‘swap out’ the criteria in a DSUM or any other database function. Very very powerful indeed. So you can set up amazing filtering run directly off the spreadsheet in a way that users can easily interact with, and do stuff that would otherwise require SQL. Blows pivots out of the water, on account that pivots only allow additive filtering.

    Hui has a great article on DataTables here http://chandoo.org/wp/2010/05/06/data-tables-monte-carlo-simulations-in-excel-a-comprehensive-guide/ which is amazing. See the section where he talks about multi input (more than 2) data tables. Hat tip to Sam for that link.

  7. Just following up on this, 4 years later! If you are looking for one or more words anywhere within a cell, and you want to return a TRUE or FALSE if a word exists, you can perform the function below where A1 is the cell you’re searching in:

    =IF(SUMPRODUCT(–ISNUMBER(SEARCH({“word1″,”word2″,”etc”},A1)))>0

    You can then manipulate this with various SQL-like IF/THEN statements to classify, extract, and label rows.

Leave a Reply

Your email address will not be published. Required fields are marked *