Skip to main content

Filter function

Table of contents
  1. Syntax
  2. Settings
  3. Examples

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


Filter(sourcearraymatch, [ include, [ compare ]])

The Filter function syntax has these named arguments:

Part Description
sourcearray Required. One-dimensional array of strings to be searched.
match Required. String to search for.
include Optional. Boolean value indicating whether to return substrings that include or exclude match. If include is TrueFilter returns the subset of the array that contains match as a substring. If include is FalseFilter returns the subset of the array that does not contain match as a substring.
compare Optional. Numeric value indicating the kind of string comparison to use. See Settings section for values.


The compare argument can have the following values:

Constant Value Description
vbUseCompareOption -1 Performs a comparison by using the setting of the Option Compare statement.
vbBinaryCompare 0 Performs a binary comparison.
vbTextCompare 1 Performs a textual comparison.
vbDatabaseCompare 2 Microsoft Access only. Performs a comparison based on information in your database.

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


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

Leave a comment

Your email address will not be published. Required fields are marked *

Format your code: <pre><code class="language-vba">place your code here</code></pre>