wildcards

Wildcards are characters that you can use to represent one or more characters. You can use the wildcards ? (question mark), * (asterisk) and ~ (swung dash) to match one or more other characters in an expression.

• Use the ? character to match a single character in an expression that allows conditions. For example:

  • “Ea?” matches any string beginning with “Ea” and containing exactly one additional character such as “Ea2” or “Eac”.

  • “Th??” matches any string beginning with “Th” and containing exactly two additional characters such as “Then” and “That”.

  • =COUNTIF(B2:E7,“?ip”) returns a count of the number of cells in the range B2:E7 that contain a value that starts with a character followed by “ip” such as “rip” and “tip”. It doesn’t match “drip” or “trip”.

• Use the * character to match any number of characters, including none, in an expression that allows conditions. For example:

  • “*ed” matches a string of any length ending with “ed” such as “Ted” or “Treed”.

  • =COUNTIF(B2:E7,“*it”) returns a count of the number of cells in the range B2:E7 that contain a value that ends with “it” such as “bit” and “mit”. It doesn’t match “mitt”.

• Use the ~ character to specify that the following character should be matched rather than used as a wildcard, in an expression that allows conditions. For example:

  • “~?” matches the question mark, instead of using the question mark to match any single character.

  • =COUNTIF(E,“~*”) returns a count of the number of cells in column E that contain the asterisk character.

  • =SEARCH(“~?”,B2) returns 19 if cell B2 contains “That is a question? Yes it is!”, since the question mark is the 19th character in the string.