Wildcard Characters in a Formula
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:
The wildcard character can only be used with an equal string.
The following code sample uses the wildcard character to search for items that contain an "a".
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*"