How to insert a wildcard?

We will learn to insert a wildcard in Excel.

What is a Wildcard?

A wildcard is a special character that lets you perform “fuzzy” matching on text in your Excel formulas.

For example, this countif wildcard formula: =COUNTIF(A2:A9,”*sub”)

Asterisk (*) Wildcard

  • The asterisk (*) represents any number of characters (including zero characters).
  • You can use it to match text strings that start, end, or contain a specific sequence of characters.

Question Mark (?) Wildcard

  • The question mark (?) represents a single character.
  • You can use it to match text strings where you want to specify a single character, but the character can be different.

Combining Wildcards

You can combine wildcards to create more complex patterns for matching text. For example, you can use both asterisks and question marks in the same formula.

Please find below some of the wildcard examples we generally use:

example wildcard usage

Using Wildcards in Other Functions

You can use wildcards in various Excel functions such as IF, SEARCH, MATCH, INDEX, VLOOKUP, and more.

For example, if you want to search for cells that contain “foobar” anywhere in the text, you can use the following formula:

=IF(ISNUMBER(SEARCH(“*foobar*”,A1)), “Yes”, “No”)

Here, the asterisks are acting as wildcard characters and the formula will return “Yes” if the text “excel” is found anywhere in cell A1, and “No” if it’s not.

Examples

Now, suppose we want to count the texts in column A which contains the word “sub”, this can be easily done by using wildcard “*sub” in the countif formula: =COUNTIF(A2:A9,”*Sub”)

See also  Kj to kcal and kcal to kj converter

wildcard countif formula

Another example is question mark usage with Countif formula =COUNTIF(A2:A9,”?????”)

This will count the cells with 5 alphabet strings:

five alphabet strings