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, for example by formulas, you need to use them as plain text cells in the following form:

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

Elements in brackets are optional.

• filter-type
  
  • a - regular expression
  • b - starts with / 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 or 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 (includes cells with spaces only)
• (optional) sort-key is a sort key index. For single-column sorting it is always 1. For multi-key sorting these must be numbers from 1 to 6, and each number may appear only once in the filters array. The number must be followed by + or - to indicate ascending or descending sort order.

• (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 (ignore punctuation and non-word characters when sorting or searching duplicates)

  Thus n can be any number from 1 to 7.

• The : or = character indicates the start of the filter text. If it is "=", the text is assumed to be a cell reference (for j: between filters, two references are required).
• (optional) filter-text is the actual filtering expression. It is ignored for filter types n and o. For 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