The like operator is used to compare text to a given pattern. It is especially useful when filtering data, with logical conditions (e.g., in cells of logical value type), and in formulas.
Main features of the operator like:
- compares text only - works only on text data
- ignores case - comparison does not distinguish between lowercase and uppercase letters e.g. like 'abc' will also match 'Abc', 'ABC' or 'aBc'
- can be used
- in filters using lookup formula
- in logical formulas (e.g. if)
- in cells of logical value type (true or false)
It is worth noting that the like operator should be written in lowercase everywhere otherwise the comparison will not work.
Special characters in like
Three special operators can be used in the like syntax to effectively search for data by pattern:
1. ? - replaces one arbitrary character - it can be a letter, a number or a special character.
Example:
like 'user??@example.com'
It means that the text must start with “user”, followed by exactly two characters (for example, “_a”, “1!”, ‘x2’), and then end with the string “@example.com”.
2. * - replaces any string of characters - can be letters, numbers or special characters
Example:
like 'user_*'
Indicates that the text must start with “user_” and can be followed by any number of characters, for example, “user_admin”, ‘user_1’, “user_123@example.com”.
3. \ - escape means treating the special character literally
Example:
like '\*?
If you want , special symbols such as ?, * or \* to be treated as ordinary characters (and not as pattern elements), you must precede them with a slash \*. For example, a pattern like ‘\*?’ will match the text ‘*?’ exactly.
If we want the slash character \* also to be interpreted literally, type it as \*. This way, like ‘\*’ will match the value that contains a single \* character.
Usage examples:
=lookup(TABLE_DATA, EMAIL like ‘*@qalcwise.com’)
=if(section20.a1 like ‘error_??_2025’,'Critical error',‘ok’)
=section21.a1 like ‘file??2025.csv’ (logical value type cell)
=section1.a1 like section2.a1
=section11.a1 like concat(section11.a2,‘*’)
0 Comments