wild cards

You can use wild cards to match any single character or multiple characters in an expression. The wild cards you can use are ? (question mark), * (asterisk) and ~ (tilde).

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

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

  “Th??” will match 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 does not match “drip” or “trip”.

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

  “*ed” will match 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 does not match “mitt”.

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

  “~?” will match 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.