Wildcards in Excel

Add to Favorites
Author:

Wildcards are characters that allow you to make more robust functions, searches, and filters in Excel. You put wildcards in functions, usually in the criteria argument, and this allows you to find partial matches for words, numbers, and characters.

Wildcards are used to make more sophisticated partial matches.

Sections:

Wildcards

Question Mark Example

Asterisk Example

Tilde Example

Referencing Cells

Other Examples

Functions that Can Use Wildcards

Notes

Wildcards

Character Meaning
?

Question mark. This matches any character. So, "St?ff" would match "Stuff" "Staff" "Stiff" etc.

*

Asterisk. This matches everything that comes either before, after, or in the middle of a value, or both. So, "*is*" would match "This is" "This is stuff" "is stuff" etc. You can have one asterisk before a value, after a value, or both at the same time.

~

Tilde. This is used to tell a function to literally match a question mark or asterisk. To match a question mark, do this "~?" and to match an asterisk do this "~*" and to match a tilde with a question mark or with an asterisk do this "~~?" or this "~~*".

Question Mark Example

=SUMIF(A1:A5,"st?ff",B1:B5)

bf183e27a0af5ac905b4990cc8567ad3.png

Result:

4c41ea17b5d9bec93acc72d573c118c2.png

This SUMIF function summed every value that started with "st" then had any character for the third character and then ended with "ff". This means that the values for "stuff" and "staff" were summed, which equals 3.

Asterisk Example

=SUMIF(A1:A5,"*a*",B1:B5)

305856aa05ee8d0bdaeecb5c88d09edf.png

Result:

e336ea48e81387b1d287f3af0482de95.png

Using the asterisk, with it on the front and back of the letter "a", every value that has an "a" in it was matched and the SUMIF function summed the values for it. In this case, "staff" and "that" were matched and so the result is 6.

Remember, with the asterisk, I can put one in front if I want to match something at the end of the cell, one at back if I want to match something at the beginning of the cell, or one at the back and front if I want to match something that is located anywhere in the cell, including even just looking for a single letter within a cell, as I did in this example.

Tilde Example

=SUMIF(A1:A7,"~?",B1:B7)

09c483633c7a611a811810f731657f3b.png

Result:

8c1da3e4056d6d1b0480d1d8629a40cf.png

Using the tilde, the SUMIF function matches only the cell that contains the single question mark within it.

If I removed the tilde from this example, the result would be 13 since the question mark is a wildcard that means it will match any single character and the "a" in cell A6 is a single character.

Referencing Cells

In the examples here, the wildcards were input next to the text or numbers and within double quotation marks.

However, if, instead of hardcoding the values, you reference a cell that has the value in it for the criteria, you must put the wildcards inside of their own double quotation marks and connect them with the cell reference using ampersands.

Here is an example using two asterisks:

=SUMIF(A1:A5,"*" & E5 & "*",B1:B5)

020f7a2687b6c931ac018636ea2623ae.jpg

Result:

3e7c2963266874b4ab9cb347fe65fcfd.jpg

This works the same if you use a question mark instead of an asterisk.

Other Examples

Here are some of our other tutorials that use wildcards:

Vlookup Partial Match in Excel

Count the Number of Cells that Contain Specific Text in Excel

Count the Number of Cells that Start or End with Specific Text in Excel

Functions that Can Use Wildcards

AVERAGEIF

AVERAGEIFS

COUNTIF

COUNTIFS

DPRODUCT

DSTDEVP

DSUM

DVARP

HLOOKUP

MATCH

SEARCH

SUMIF

SUMIFS

VLOOKUP

Notes

Wildcards can be intimidating at first and they are often used in not-so-simple functions and formulas, but, once you get used to using them, they can save you a lot of time and headaches. Plus, you only have to remember three wildcards and then how to use them.

Make sure to download the sample file for this tutorial so you can work with these examples in Excel.


Excel Function: SUMIF()
Downloadable Files: Excel File

Question? Ask it in our Excel Forum


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Similar Content on TeachExcel
SUMIF - Sum Values Based on Criteria in Excel
Tutorial: The SUMIF function allows you to sum values based on a single criteria. This function wor...
SUMIFS - Sum Values Based on Multiple Criteria in Excel
Tutorial: The SUMIFS function allows you to sum values that meet multiple criteria across multiple ...
Partial Match Lookup with Numbers in Excel
Tutorial: Perform lookups on numbers with partial matches. For instance, find the first number that...
SEARCH() vs FIND() Function in Excel
Tutorial: The differences between FIND() and SEARCH() and when to use these functions in Excel - th...
How to Quickly Find Data Anywhere in Excel
Tutorial: Finding specific records and/or cells is easy when using the Find tool in Excel. It is lo...
Remove All HTML from Text in Excel
Tutorial: How to quickly and easily remove all HTML from data copied into Excel. This tutorial inclu...
Tutorial Details
Excel Function: SUMIF()
Downloadable Files: Excel File
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course