Navigation:  Formula Reference > Formula OverView >

Wildcard Characters in a Formula

Previous pageReturn to chapter overviewNext page

 

You can use the wildcard character in formulas to search for values. The wildcard character can be used in formulas that have a criteria argument.

The following wildcard characters can be used as comparison criteria for functions when searching.

Character

Finds

Example

? (question mark)

Any single character

sm?th finds "smith" and "smyth"

* (asterisk)

Any number of characters

*east finds "Northeast" and "Southeast"

~ (tilde) followed by ?, *, or ~

A question mark, asterisk, or tilde

fy91~? finds "fy91?"

The following formulas support the wildcard character:

AVERAGEIF
AVERAGEIFS
COUNTIF
COUNTIFS
DAVERAGE
DGET
DMAX
DMIN
DPRODUCT
DSTDEV
DSTDEVP
DSUM
DVAR
DVARP
HLOOKUP
MATCH
SEARCH
SUMIF
SUMIFS
VLOOKUP

The wildcard character can only be used with an equal string.

Using Code

This example uses the wildcard character to search for items that contain an "a".

JavaScript

Copy Code

activeSheet.setValue(0, 0, 'abc');
activeSheet.setValue(1, 0, 'ac');
activeSheet.setValue(2, 0, 'a*');
 
activeSheet.getCell(0, 1).formula('COUNTIF(A1:A3,"a*")'); //start with a
activeSheet.getCell(1, 1).formula('COUNTIF(A1:A3,"a?")'); //"a" and only one other character
activeSheet.getCell(2, 1).formula('COUNTIF(A1:A3,"a~*")'); // should be "a*"