How to use the wildcards in Excel

In this article, we will learn How to use the wildcards in Excel.

Partial match up in Excel

In Excel, sometimes you find yourself with problems where you need to perform an operation over some values having criteria. For example finding the sum of sales of employees who belong from the B department where department names are included in Employee ID (0234-B-2019). For these kinds of problems, excel provides you with three wildcards.

  1. Question mark ( ? ) : This wildcard is used to search for any single character. This is used when you know the exact count of matchup value. Like matching phone numbers (8 digit) which starts with 999 (use "999?????".
  2. Asterisk ( * ): This wildcard is used to find any number of characters preceding or following any character. This is the most used wildcard. Like matching domain names in urls (use "*@domain.*) .
  3. Tilde ( ~ ): This wildcard is an escape character, used preceding the question mark (?) or asterisk mark (*). This used when we need to match * or ? chars.

These can be used either in criterias or in the filter options to filter directly using wildcard. Let's understand how to use them with functions or with filter tool to filter results.

Example :

All of these might be confusing to understand. Let's understand how to use the function using an example. Here we have the ID records and we need to find the IDs which ends with A category.

Here to find the A category IDs, will be using the * (asterisk) wildcard in the formula. * (asterisk) wildcard finds any number of characters within lookup value.

Use the formula:

= COUNTIF ( C3:C14, "*A" )


= COUNTIF ( C3:C14, "*" & E4 )


  • COUNTIF function count the cells given criteria
  • Criteria is given in using * (asterisk) wildcard to look for value which has any number of characters.
  • & operator concatenate the two values where it lays
  • "*" & E4 : criteria, formula checks each cell and returns only the cells which satisfies the criteria

Here the range is given as array reference and pattern is given as cell reference. Press Enter to get the count.

As you can see the total values which ends with value A comes out to be 4.


As you can see all the different category count of IDs is there.

You can check which values end with a specific pattern in range using the excel filter option. Apply the filter to the ID header and click the arrow button which appears. Follow the steps as shown below.


  1. Select the ID header cell. Apply filter using shortcut Ctrl + Shift + L
  2. Click the arrow which appeared as a filter option.
  3. Deselect (Select All) option.
  4. Now search in search box using the * (asterisk) wildcard.
  5. Type *A in the search box and select the required values or select the (Select All search result) option as shown in the above gif.

IF function with wildcards

Here we have company codes on one side. And we need to find the fields where the phrase AT is present anywhere in the code.

IF function doesn’t support wildcards so we will be using SEARCH function. SEARCH function returns a number if the phrase is present within the text.

Use the formula:

=IF(ISNUMBER(SEARCH("*AT*",A2)), "AT", "")

SEARCH function accepts the wildcard (*) and finds the phrase “AT”, within A2. It returns a number if SEARCH finds the phrase.

ISNUMBER function finds the number and returns TRUE.

IF function logic_test results in TRUE and FALSE and returns “AT” if True or “”(empty string) if False.

As you can the formula catches the AT in A2 cell and returns the phrase. This shows A2 cell has “AT” pharse within text.

Now use the formula in other cells to get all fields containing the phrase “AT”.

As you can see we used wildcards with IF function to get the result.


Related Articles :







Popular Articles :





Leave a Reply


Terms and Conditions of use
