Filter function

Returns a zero-based array containing a subset of a string array based on a specified filter criteria.

Syntax

Filter(sourcearray, match, [ include, [ compare ]])

The Filter function syntax has these named arguments:

Settings

The compare argument can have the following values:

The array returned by the Filter function contains only enough elements to contain the number of matched items.

Examples

The Filter function makes it easy to search a string array if you simply need to know whether an item exists in the array. The Filter function takes a string array and a string containing the search text. It returns a one-dimensional array containing all the elements that match the search text.

One potential disadvantage of using the Filter function to search an array is that it doesn't return the index of the elements of the array that match the search text. In other words, the Filter function tells you whether an element exists in an array, but it doesn't tell you where.

Another potential problem with using the Filter function to search an array is that there's no way to specify whether the search text should match the entire element, or whether it need only match a part of it. For example, if you use the Filter function to search for an element matching the letter "e," the Filter function returns not only those elements containing only "e," but also any elements containing larger words that include "e."

The following procedure augments the capabilities of the Filter function in order to search an array and returns only elements that match exactly. The FilterExactMatch procedure takes two arguments: a string array to search and a string to find. It uses the Filter function to return an array containing all elements that match the search string, either partially or entirely. It then checks each element in the filtered array to verify that it matches the search string exactly. If the element does match exactly, it's copied to a third string array. The function returns this third array, which contains only exact matches.

Function FilterExactMatch(astrItems() As String, strSearch As String) As Variant
                  
   ' This function searches a string array for elements
   ' that exactly match the search string.

   Dim astrFilter()   As String
   Dim astrTemp()       As String
   Dim lngUpper         As Long
   Dim lngLower         As Long
   Dim lngIndex         As Long
   Dim lngCount         As Long
   
   ' Filter array for search string.
   astrFilter = Filter(astrItems, strSearch)
   
   ' Store upper and lower bounds of resulting array.
   lngUpper = UBound(astrFilter)
   lngLower = LBound(astrFilter)
   
   ' Resize temporary array to be same size.
   If lngUpper > -1 Then
   
        ReDim astrTemp(lngLower To lngUpper)
        
        ' Loop through each element in filtered array.
        For lngIndex = lngLower To lngUpper
           ' Check that element matches search string exactly.
           If astrFilter(lngIndex) = strSearch Then
              ' Store elements that match exactly in another array.
              astrTemp(lngCount) = strSearch
              lngCount = lngCount + 1
           End If
        Next lngIndex
        
         ' Resize array containing exact matches.
         If lngCount > 0 Then
             ReDim Preserve astrTemp(lngLower To lngCount - 1)
         
             ' Return array containing exact matches.
             FilterExactMatch = astrTemp
         End If
    Else
        FilterExactMatch = "None found"
    End If
End Function


Sub test()
    Dim arr() As String
    arr = Split("Bangalore,Mumbai,Hydrabad,Hydrabad,Orissa", ",")
    
    Dim IsInArray As Variant
    IsInArray = FilterExactMatch(arr, "Mumbai")
'    IsInArray = FilterExactMatch(arr, "M")
'    IsInArray = FilterExactMatch(arr, "MMM")

    If IsArray(IsInArray) And Not IsEmpty(IsInArray) Then
        MsgBox IsInArray(0)
    Else
        MsgBox "None found"
    End If
End Sub

Another Exact Match solution:

Sub ExactMatch()
    Dim arr() As String
    arr = Split("Bangalore,Mumbai,Hydrabad,Hydrabad,Orissa", ",")
    
    Dim IsInArray As Boolean
    IsInArray = Not IsError(Application.Match("Mumbai", arr, 0))
    MsgBox IsInArray
End Sub