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.

11 Comments

  1. J-Walk says:

    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. tuxedobuford says:

    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. tuxedobuford says:

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

    Thanks.

  4. Andrea says:

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

  5. Bruce says:

    Thanks Dick, you’re a lifesaver!

  6. Mike says:

    Anyone know how to simulate a sql “in” operator in Excel?

  7. Pete says:

    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

  8. Camille says:

    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”))

  9. Jeff Weir says:

    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.

  10. Alasdair C-S says:

    Works for me! Thanks very much.

  11. Scott Taylor says:

    The SEARCH command works for Text

Posting code or formulas in your comment? Use <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

Here's how to update your reports of company and nearly any web data: