While discussing the limitations of wild card comparisons in the Microsoft Excel’s
IF function, I resolved to share my
ISLIKE function. It acts like the
LIKE operator in SQL. You can’t use the word like as the function name because it’s a reserved word in Visual Basic for Applications (VBA).
The function lets you compare any string to see if it is contained in another string. The default mode is case-insensitive, and the override mode case-sensitive. The optional third element lets you override the default comparison method.
You should remember or note that a number
0 is always evaluated as false, and any other number (negative or positive) is always true. You can put the
ISLIKE function in a macro sheet or an Add-in library. I recently posted instructions on how to create your own Add-in library.
Here is the VBA code for the user defined
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27
Function ISLIKE( inValue As Variant _ , hasValue As Variant _ , Optional caseSensitive As Integer = 0) ' Define a logical return variable and assign a default value. Dim logicReturn As Boolean logicReturn = False If caseSensitive Then ' Finds a case-insensitive value as a component in a larger value. If inValue Like "*" + hasValue + "*" Then logicReturn = True End If Else ' Finds a case-sensitive value as a component in a larger value. If UCase(inValue) Like "*" + UCase(hasValue) + "*" Then logicReturn = True End If End If ' Return the logical value. ISLIKE = logicReturn End Function
Line numbers 12 and 19 use the
"*", which is equivalent to the regular expression of
"\.*". I’ve no idea why they made this choice, and it’s not well explained on Microsoft’s web site covering SmartTags.
You can shorten the program and make it slightly less efficient by performing the case sensitive match twice. The following doesn’t rely on nested block
IF statements. You also must change the third parameter to a
Boolean or the
Not operator won’t work correctly.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
Function ISLIKE( lookupValue As Variant _ , containsValue As Variant _ , Optional caseSensitive As Boolean = 0) ' Define a logical return variable and assign a default value. Dim logicReturn As Boolean logicReturn = False ' Finds a case-insensitive value as a component in a larger value. If caseSensitive And _ lookupValue Like "*" + containsValue + "*" Then logicReturn = True ' Finds a case-sensitive value as a component in a larger value. ElseIf Not caseSensitive And _ UCase(lookupValue) Like "*" + UCase(containsValue) + "*" Then logicReturn = True End If ' Return the logical value. ISLIKE = logicReturn End Function
Somebody asked me, “How this can be useful?” That meant from their perspective, how is it useful beyond a comparison of cell values. A quick example of that additional utility can be demonstrated from an early post. In that post on replacing a
VLOOKUP function with a UDF, there’s compound logic used to find the occurrence of a substring in a string. The
ISLIKE function can simplify that logic.
Here’s that snippet of the code from that referenced post. The snippet evaluates whether the base string is greater than one character and checks the second character of the string to see if it is a
- (minus sign).
ElseIf Len(LetterGrades(i)) > 1 And Mid(LetterGrades(i), 2, 1) = "-" Then
You can replace the compound logic with a call to the
LIKE function. It would replace line 58 as follows:
ElseIf ISLIKE(LetterGrades(i),"-") Then
The logic of the function examines to see if the first parameter contains the second parameter. This is what is meant by like. In the following illustration, you examine if a string contains a substring (or is like a substring). The cell
A2 is also assigned a Range name. Range names can increase the readability of your models and functions. All but the last one perform case insensitive comparisons that find whether the substring in cell
A2 is found with cell range of the
B10 formula performs a case sensitive match.
Another logic comparison uses grades to see if a minus sign is found in them. The example compares the cell values in
A1 against the
B2 values. Returning true for one and false for the other cases in column
C of the respective rows.
Here are the calls to the
ISLIKE user defined function. The first one is case-insensitive and the second case-sensitive, but there’s no case sensitivity with a minus sign,
As always, I hope this helps. If you’ve comments or alternative approaches, please let me know.