Utilizar comodines en fórmulas

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.

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.

Se admiten los comodines siguientes:

Comodín	Descripción
? (signo de interrogación)	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.
* (asterisco)	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.
~ (virgulilla)	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".

Comodín

Descripción

? (signo de interrogación)

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.

* (asterisco)

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.

~ (virgulilla)

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".

Los comodines pueden utilizarse tanto en LibreOffice Calc como en Microsoft Excel. Por esta razón, si necesita interoperatividad entre ambas aplicaciones, opte por los comodines y no por las expresiones regulares. Por el contrario, si la interoperatividad no es necesaria, considere utilizar expresiones regulares para obtener prestaciones de búsqueda más avanzadas.

Funciones de hoja de cálculo admitidas

Las funciones de hoja de cálculo siguientes admiten comodines:

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.

Ejemplos de comodines en fórmulas

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.

Las comparaciones mediante comodines no distinguen mayúsculas y minúsculas, por lo cual «A?» encontrará tanto «A1» como «a1».

Lista de expresiones regulares