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.
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)
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.
Found it..COUNTIF(RANGE,”*” & CELL & “*”)
whereas RANGE is my search range and CELL contains the string I’m counting.
Thanks.
Thanks. I’ve been trying to figure out how to do this in Excel for an eternity.
Thanks Dick, you’re a lifesaver!