How to: Apply a Custom Text Filter

• Sep 19, 2023
• 2 minutes to read

This example demonstrates how to specify the custom compound filter criteria to filter text values in a column.

1. Turn on the filtering functionality for the required range, as described in the How to: Enable Filtering example.
2. Use the AutoFilterBase.Columns property of the SheetAutoFilter object to get a collection of columns in the filtered range (the AutoFilterColumnCollection object). Each column in the collection is defined by the AutoFilterColumn object, which provides basic methods for data filtering. To filter data in a particular column, get access to this column by its index in the AutoFilterColumnCollection collection.

3. To apply a custom complex filter, call the AutoFilterColumn.ApplyCustomFilter method overload with five parameters.

   • Specify the first filter criterion. To perform text filtering, use the wildcard characters. The asterisk * matches any number of characters, while the question mark ? represents a single character. For example, to display all the products that contain the two-letter combination "Gi" in their names, specify the "Gi" string as the filter criterion value, and use the FilterComparisonOperator.Equal member of the FilterComparisonOperator enumeration as the comparison operator.

     Tip

     To filter values containing a specific character, such as the asterisk, question mark or tilde, put the tilde (~) before it.

   • Specify the second filter criterion. To include blank cells in the filtering results, use the value of the FilterValue.FilterByBlank property as the second criterion value, and select the FilterComparisonOperator.Equal comparison operator.
   • Pass the false value as the last parameter of the method to indicate that the OR operator should be used to combine the filter criteria specified above.

View Example

AutoFilterActions.cs

Worksheet worksheet = workbook.Worksheets["Regional sales"];
workbook.Worksheets.ActiveWorksheet = worksheet;

// Enable filtering for the specified cell range.
CellRange range = worksheet["B2:E23"];
worksheet.AutoFilter.Apply(range);

// Filter values in the "Product" column that contain "Gi" and include empty cells.
AutoFilterColumn products = worksheet.AutoFilter.Columns[1];
products.ApplyCustomFilter("*Gi*", FilterComparisonOperator.Equal, FilterValue.FilterByBlank, FilterComparisonOperator.Equal, false);

AutoFilterActions.vb

Dim worksheet As Worksheet = workbook.Worksheets("Regional sales")
workbook.Worksheets.ActiveWorksheet = worksheet

' Enable filtering for the specified cell range.
Dim range As CellRange = worksheet("B2:E23")
worksheet.AutoFilter.Apply(range)

' Filter values in the "Product" column that contain "Gi" and include empty cells.
Dim products As AutoFilterColumn = worksheet.AutoFilter.Columns(1)
products.ApplyCustomFilter("*Gi*", FilterComparisonOperator.Equal, FilterValue.FilterByBlank, FilterComparisonOperator.Equal, False)