You can filter text fields with one of these operators:
- Is in / Is not in
- Contains / Doesn't contain
- Starts with / Doesn't start with
- Ends with / Doesn't end with
- Like / Not like
Choose an operator (1) and choose one or more values (2).
Is in / Is not in
You can input multiple values if you choose is in or is not in. Rows will show if they match ANY of the values. In our example above, rows will show whether the product type is 'Art' or 'Clothing'.
Similarly, you can use is not in to find rows that don't match ANY of the values (in other words, rows that match NONE of the values).
Is in and Is not in also allow special built-in operands: [Unknown] and <empty>.
Use [Unknown] to match all rows that don't have a value (the value is NULL).
Use <empty> to match all rows with a blank (empty string) value.
Don't type '[Unknown]' or '<empty>' with the keyboard. Instead, always select it from the drop-down list.
Like / Not like
Like and not like are powerful operators that use special wildcard characters to match specific patterns.
Wildcard character | Description | Example |
% | Any string of zero or more characters | %computer% finds rows with the word 'computer' |
_ (underscore) | Any single character | _ean finds rows with a 4 characters value that ends in 'ean'. (Dean, Sean, and so on). |
[ ] | Any single character within the specified range (e.g. [a-f]) or set (e.g. [abcdef]) | [C-P]arsen finds rows that end with 'arsen' and start with any single character between C and P (Carsen, Larsen, Karsen, etc). |
[^] | Any single character not within the specified range (e.g. [^a-f]) or set (e.g. [^abcdef]) | [^abc]ing finds rows that end in 'ing' and don't start with 'a' or 'b' or 'c' ('sing', 'king', etc). |
[#-#] | Any single number within the specified range (e.g. [0-9]). | [0-9][0-9][0-9]R% finds values with 3 numeric characters followed by text starting with R |
You can also use multiple wildcards at a time.
For example, [a-f]_[^et]%ing% finds rows that start with a character between 'a' and 'f', followed by any character, followed by a character that is neither 'e' or 't', followed by any characters, followed by 'ing', followed by any characters.