Excel Wildcards

In this article, we will learn about how and when to use Wildcard characters or in-built regex (regular expressions) characters for excel functions.

Excel lets you use Wildcard characters for catching strings and perform functions on it.

There are three wildcard characters in Excel

  1. Question mark (?) : This wildcard is used to search for any single character.
  2. Asterisk (*): This wildcard is used to find any number of characters preceding or following any character.
  3. Tilde (~): This wildcard is an escape character, used preceding the question mark (?) or asterisk mark (*).

Let’s use these wildcards in examples

Here we need products which starts with C. So first we will apply filter to the list using Ctrl + Shift + L. Then in the Product search box type “C*” as shown below.
1
As you can see the whole list is filtered with all the products Starting with C.

Note: * (wildcard) finds any number of characters.

Here we need Quantity values range from 30-39. So first we will apply the filter to the list using Ctrl + Shift + L. Then in quantity search box type “3?” as shown below.
2
As you can see the whole list is filtered with all the products Starting with C.

Note: ? (wildcard) finds single characters.

Tilde (~) wildcard is used when we need to find the symbol question mark or asterisk (not as wildcards). For example if you are searching a string which ends with question mark. You type “ *~?” .This will find you all strings with question mark.

Here we have need to find data ending with *(astrick). So we will apply two wildcards *~* . The first astrick is used as wildcard and the second one is used to find the astrick sign in the data.
3
Note: ~ (wildcard) finds the two characters * & ?

Not all functions can execute Wildcards with Excel functions.
Here is a list of functions that accept wildcards.

Hope you understood how to use Wildcards in Excel. Explore more articles on Excel functions and formulas here. Mention your queries in the comment box below. Thank you.

Related Articles:

How to Get Position of First Partial Match

Partial match with VLOOKUP function

Popular Articles:

50 Excel Shortcuts to Increase Your Productivity

The VLOOKUP Function in Excel

COUNTIF in Excel 2016

How to Use SUMIF Function in Excel

 

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube