Filtering Lists with the FILTER functions and the LIKE operators

As discussed in previous articles, when you retrieve values from multiple items simultaneously, you often receive a list of values, which are separated by a pipe (|), our designated list separator. You can use the FILTER function to narrow these values down to those that interest you.

You can use 2 functions to filter lists. FILTER and POSITIONFILTER. Both work the same way. The first one retrieves the values, the second their positions.

How the FILTER and POSITIONFILTER functions work

They both require 2 parameters. The 1st parameter is the condition used to filter the values and the 2nd is the list of values to filter.

The construction of the filter condition is simple: the keyword _each_ followed by an operator and a value. Here are a few examples:

These functions operate like a loop, generating a new list. Each value in the original list is evaluated against the condition and, if it meets the condition, it (or its position) is included in the output list.

List of operators that can be used in the filter condition

You can use the arithmetics operators: =,>,<,>=,<=,<>, as well as the LIKE operators: LIKE, NOT LIKE, CSLIKE and NOT CSLIKE.

The LIKE operator

The LIKE operator is particularly useful for searching for patterns within text fields. It supports several special characters and wildcard patterns that help refine search criteria:

If you are familiar with SQL Server, the LIKE operator works the same way.

LIKE is case-insensitive.

1. Percent (%): Represents zero, one, or multiple characters.
   Example: LIKE "Jo%"" matches any name starting with "Jo", such as "John", "Jonathan", "Jo". Since LIKE is case-insensitive, it would match "john", "jonathan" and "jo" as well.
2. Underscore (_): Represents a single character.
   Example: LIKE "J_n" matches "Jan", "jan", "Jen", "jen", "Jon", etc.
3. Square Brackets ([ ]): Represents any single character within the brackets.
   Example: LIKE "J[oa]n" matches "Jan" and "Jon".
4. Caret (^): When used inside square brackets, it negates the character set.
   Example: LIKE "J[^oa]n" matches "Jen" and "Jun", but not "Jan" or "Jon".
5. Dash (-): Used inside square brackets to specify a range of characters.
   Example: LIKE "[A-C]%" matches any name starting with A, a, B, b, or C, c.

The CSLIKE operator

The CSLIKE operator is the case-sensitive version of LIKE.

The UNLIKE and CSUNLIKE operators

To obtain results that are the opposite of LIKE, use UNLIKE.

To obtain results that are the opposite of CSLIKE, use CSUNLIKE.

Here are a few examples:

To keep only the values ending with 100:

To keep only the values starting with E:

To keep only the values starting with E, e followed by either A, a, B or b as 2nd character:

To keep only the values that don't start with E, e followed by either A, a, B or b as 2nd character:

To keep only the values starting with E followed by either A or B as 2nd character::