Using Wildcards in Formulas /text/scalc/guide/wildcards.xhp wildcards in formulas wildcards;examples

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. %PRODUCTNAME Calc supports either wildcards or regular expressions as arguments depending on the current application settings. By default %PRODUCTNAME Calc is set to support wildcards instead of regular expressions. To make sure wildcards are supported, go to %PRODUCTNAME - Preferences - %PRODUCTNAME Calc - CalculateTools - Options - %PRODUCTNAME Calc - Calculate 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 %PRODUCTNAME settings. If the option Search criteria = and <> must apply to whole cells is disabled in %PRODUCTNAME - Preferences - %PRODUCTNAME Calc - CalculateTools - Options - %PRODUCTNAME 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 %PRODUCTNAME 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 %PRODUCTNAME - Preferences - %PRODUCTNAME Calc - CalculateTools - Options - %PRODUCTNAME 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".