MacLochlainns Weblog

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 ISLIKE function:

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

58

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:

58

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 B5:B10. 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 and A2 against the B1 and 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.