Use string operators and wildcards
The string operator (&) can be used in formulas to concatenate, or join, two or more strings or the contents of referenced cells. Wildcards (*,?,~) can be used in conditions to represent one or more characters.
Concatenate strings or the contents of cells
The & character is used to concatenate, or join, two or more strings or the contents of referenced cells.
Some examples of the use of the concatenation operator are:
"Abc"&"Def" returns "AbcDef".
"Abc"&A1 returns "Abc2" if cell A1 contains 2.
A1&A2 returns "12" if cell A1 contains 1 and cell A2 contains 2.
B2&", "&E2 returns "Last, First" if B2 contains "Last" and E2 contains "First".
Use a wildcard to match any single character
The ? character is used to match a single character in an expression that allows conditions.
Some examples of the use of the wildcard character ? in matching patterns are:
"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 a wildcard to match any number of characters
The * character is used to match any number of characters, including none, in an expression that allows conditions.
Some examples of the use of the wildcard character * in matching patterns are:
"*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 does not match "mitt".
Match a wildcard character
The ~ character is used to specify that the following character should be matched rather than used as a wildcard, in an expression that allows conditions.
Some examples of using the character ~ in matching patterns are:
"~?" 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.
Use multiple wildcard characters in a condition
The wildcard characters (? * ~) can be used together in expressions that allow conditions. Some examples are:
"*a?" matches any expression that contains the character "a" followed by any other single character, such as "That", "Cap" and "Irregular".
COUNTIF(B2:E7,"*on?") returns a count of the number of cells in the range B2:E7 that contain a value that starts with any number of characters (including none) followed by "on" and then a single character. This matches words such as "alone", "bone", "one" and "none". This doesn’t match "only" (which has two characters after the "on") or "eon" (which has no characters after the "on").