Using Wildcards in Formulas

Wildcards are special characters that can be used in search strings that are passed as arguments to some Calc functions. They can also be used to define search criteria in the Find & Replace dialog. The use of wildcards enables the definition of more advanced search parameters with a single search string.

LibreOffice Calc supports either wildcards or regular expressions as arguments depending on the current application settings. By default LibreOffice Calc is set to support wildcards instead of regular expressions.

tip

To make sure wildcards are supported, go to and check if the option Enable wildcards in formulas is selected. Note that you can use this dialog to switch to regular expressions by choosing Enable regular expressions in formulas or choose to support neither wildcards nor regular expressions.


The following wildcards are supported:

Wildcard

Description

? (question mark)

Matches any single character. For example, the search string "b?g" matches “bag” and “beg” but will not match "boog" or "mug".

Note that it will not match "bg" as well, since "?" must match exactly one character. The "?" wildcard does not correspond to a zero-character match.

* (asterisk)

Matches any sequence of characters, including an empty string. For example, the search string "*cast" will match “cast”, “forecast”, and “outcast”, but will not match "forecaster" using default LibreOffice settings.

If the option Search criteria = and <> must apply to whole cells is disabled in , then "forecaster" will be a match using the "*cast" search string.

~ (tilde)

Escapes the special meaning of a question mark, asterisk, or tilde character that follows immediately after the tilde character.

For example, the search string "why~?" matches “why?” but will not match "whys" nor "why~s".


tip

Wildcards are supported in LibreOffice Calc and in Microsoft Excel. Therefore, if interoperability between both applications is needed, choose to work with wildcards instead of regular expressions. Conversely, if interoperability is not necessary, consider using regular expressions for more powerful search capabilities.


Supported Spreadsheet Functions

Wildcards are supported by the following spreadsheet functions:

Examples of Wildcards in Formulas

The following examples consider that the options Enable wildcards in formulas and Search criteria = and <> must apply to whole cells are enabled in .

=COUNTIF(A1:A10;"Chi*") counts the number of cells in the range A1:A10 containing strings that start with "Chi" followed by zero or more characters.

=SUMIF(A1:A5;"A??";B1:B5) sums the values in B1:B5 whose corresponding values in A1:A5 start with "A" followed by exactly two other characters.

note

Wildcard comparisons are not case sensitive, hence "A?" will match both "A1" and "a1".


Please support us!