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

Returns an array containing a subset of values that contain a substring (Variant).


sourcearrayThe array of strings to be searched (1-dimensional) (Variant / Array)
matchThe text string to search for (String).
include(Optional) Whether to return substrings that include or exclude match:
True = subset does include the match (default)
False = subset does not include the match
compare(Optional) A vbCompareMethod constant specifying the type of string comparison to use (Integer):
-1 = vbUseCompareOption (uses the "Option Compare" setting)
0 = vbBinaryCompare (default, case sensitive)
1 = vbTextCompare (not case sensitive)
2 = vbDatabaseCompare (uses an Access database)

REMARKS
* This function is case sensitive (by default).
* This function will always return a 0 based array.
* This function always returns a 1 dimensional array.
* This function ignores any Option Base 1 statements and always creates an array that starts at 0.
* This function searches an array for all the elements that match a given text string and returns an array of just the elements that match.
* There is no way to specify whether the search text should match the entire element or just part of it.
* If no matches of match are found within "sourcearray", then an empty array is returned.
* If "sourcearray" is Null, then a "type mismatch" run-time error occurs.
* If "include" = True, then the subset is all the values that does include the substring "match".
* If "include" = False, then the subset is all the values that do not include the substring "match".
* If "include" is left blank, then True is used.
* If "compare" is left blank, then -1 is used. If there is no Option Compare statement provided then vbBinaryCompare (0) is used.
* If "compare" is Null, then an error occurs.
* The array that is returned only contains enough elements to contain the number of matched items.
* This function can be used to determine if a particular element exists in a 1-dimensional array.
* You can use the ARRAY function to return an array containing given values.
* You can use the JOIN function to return a text string containing all the elements in an array.
* You can use the SPLIT function to return an array containing a specified number of substrings.
* This function can return an empty array. When this happens the lowerbound is 0 and the upperbound is -1.
* The equivalent .NET function is Microsoft.VisualBasic.Strings.Filter
* For the Microsoft documentation refer to docs.microsoft.com

Dim myArray(2) As String 
Dim SubStrings() As String
myArray(0) = "This"
myArray(1) = "is"
myArray(2) = "it"

SubStrings = Filter(myArray, "is")
Debug.Print VBA.Join(SubStrings, ",") 'This,is"

SubStrings = Filter(myArray, "is", False)
Debug.Print VBA.Join(SubStrings, ",") 'it"

SubStrings = Filter(myArray, "is", , VBA.VbCompareMethod.vbTextCompare)
Debug.Print VBA.Join(SubStrings, ",") 'This,is"

SubStrings = Filter(myArray, "is", True, VBA.VbCompareMethod.vbTextCompare)
Debug.Print VBA.Join(SubStrings, ",") 'This,is"

SubStrings = Filter(myArray, "IS")
Debug.Print "'" & VBA.Join(SubStrings, ",") & "'" 'blank

SubStrings = Filter(myArray, "IS", True, vbTextCompare)
Debug.Print "'" & VBA.Join(SubStrings, ",") & "'" 'This,is

© 2021 Better Solutions Limited. All Rights Reserved. © 2021 Better Solutions Limited Top