The easiest way to specify filters in the FILTER() function is to use the "Format > Search Expression" style for the cells containing filters.

If you want to access and construct search expressions directly e.g. by your formulas, you need to use them as plain text cells in the following form:

filter-type[sort-key+|-][n]{:|=}[filter-text]

where elements in [,] brackets are optional.

• filter-type
  
  • a - a regular expression
  • b - "starts with" / a pattern with *, ?, ~
  • c - equal
  • d - not equal
  • e - similar to
  • f - greater than
  • g - greater or equal
  • h - smaller than
  • i - smaller or equal
  • j - between (values/strings)
  • k - cell content equal to
  • l - cell content length greater than
  • m - cell content length smaller than
  • n - duplicated values in a column
  • o - empty cells (as opposed to "k:0" this will also include cells with spaces only)
• (optional) sort-key is a sort key index; for a single column sorting it's always "1"; for multi-key sorting these must be numbers from 1 to 6 where each one can occur in the "filters" array only once; the number must be followed by the "+" or "-" sort order indications (the ascending and descending sort orders);

• (optional) n is a digit representing (a sum of) options:

  • 1 - allow empty matches (required by RegEx if you want to use expressions like \A\z)
  • 2 - match case
  • 4 - word sort order; when sorting and searching for duplicates ignore punctuations and non-word characters

  Thus n can be any number from 1 to 7.

• The : or = character indicates the starting position of the filter text itself. If it's "=", it's assumed that this a cell reference containing that text (for the "between" filter: two references);
• (optional) filter-text is the actual filtering expression; it's ignored for the "n" or "o" filter types. For the other filters, specifying an empty text results in ignoring that filter.

Examples:

• a:john

• b:*smith

• e:color

• j:23 54

• j:A M

• j=$b$1 $b$2

• k:15

• n:

• o:

• a1+:john

• b1-:*smith

• e2+:color

• a5:john

• b2:*smith

• e1:color

• a1+5:john

• b1-2:*smith

• e2+1:color