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 dialogue box. The use of wildcards enables the definition of more advanced search parameters with a single search string.
Office Calc supports either wildcards or regular expressions as arguments depending on the current application settings. By default Office Calc is set to support wildcards instead of regular expressions.
To make sure wildcards are supported, go to Tools - Options - Office Calc - Calculate and check if the option Enable wildcards in formulas is selected. Note that you can use this dialogue box to switch to regular expressions by choosing Enable regular expressions in formulae 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 Office settings. If the option Search criteria = and <> must apply to whole cells is disabled in Tools - Options - Office Calc - Calculate, 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". |
Wildcards are supported in Office 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:
- Database functions: DAVERAGE, DCOUNT, DCOUNTA, DGET, DMAX, DMIN, DPRODUCT, DSTDEV, DSTDEVP, DSUM, DVAR and DVARP.
- Conditional functions: AVERAGEIF, AVERAGEIFS, COUNTIF, COUNTIFS, MAXIFS, MINIFS, SUMIF and SUMIFS.
- Lookup functions: HLOOKUP, LOOKUP and VLOOKUP.
- Other functions: MATCH and SEARCH.
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 Tools - Options - Office Calc - Calculate.
=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.
Wildcard comparisons are not case sensitive, hence "A?" will match both "A1" and "a1".