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