Ways to use the string operator and wildcards

The string operator (&) can be used in formulas, and wildcards (*,?,~) can be used in conditions.

Concatenate strings or the contents of 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.”

Using a wildcard to match any single character

Some examples of the use of ? 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.”

Using a wildcard to match any number of characters

Some examples of the use of * 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.”

Matching a wildcard character

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”).