Credit: Lucas Gouveia/How-To Geek

Published 1 minute ago

Tony Phillips is an experienced Microsoft Office user with a dual-honors degree in Linguistics and Hispanic Studies. Prior to starting with How-to Geek in January 2024, he worked as a document producer, data manager, and content creator for over ten years, and loves making spreadsheets and documents in his spare time.

Tony is also an academic proofreader, experienced in reading, editing, and formatting over 3 million words of personal statements, resumes, reference letters, research proposals, and dissertations. Before joining How-To Geek, Tony formatted and wrote documents for legal firms, including contracts, Wills, and Powers of Attorney.

Tony is obsessed with Microsoft Office! He will find any reason to create a spreadsheet, exploring ways to add complex formulas and discover new ways to make data tick. He also takes pride in producing Word documents that look the part. He has worked as a data manager in a secondary school in the UK and has years of experience in the classroom with Microsoft PowerPoint. He loves to encounter problems in Microsoft Office and use his expertise and legal-level training to find solutions.

Outside of the Microsoft world, Tony is a keen dog owner and lover, football fan, astrophotographer, gardener, and golfer.

Picture this: you need to find or count cells containing asterisks (*) or question marks (?) in Excel, but you’re getting incorrect results. This is because asterisks and question marks are wildcard characters. The solution? Excel’s essential escape character, the tilde (~).

Wildcard and escape characters in Excel

Wildcard characters are symbols that allow you to create flexible search criteria in Excel functions such as COUNTIF or SUMIF, and features such as Find and Replace and filtering. However, to use them accurately, you need to know how to use the escape character—the tilde—to search for the wildcards themselves.

The Excel wildcards

Excel recognizes two primary wildcard characters: the asterisk and the question mark.

Wildcard character	Name	What it represents	Example
*	Asterisk	Any sequence of characters (including no characters).	Searching for "A*" matches "Apple," "A," and "Accountant."
?	Question mark	Any single character at that position.	Searching for "b?t" matches "bat," "bet," "bit," and "but," but not "boot."

The problem: When wildcards mask literal characters

If your data contains an actual asterisk or question mark, and you try to search for it using a function or a feature that supports wildcards, Excel interprets the symbol as its wildcard meaning, not the literal character.

Imagine you manage an inventory for an electronics store. In your product database, you use an asterisk to signify clearance items.

You want to know exactly how many products are marked for clearance, so in a separate cell, you type:

=COUNTIF(A:A,"*")

However, the formula counts every non-empty cell in column A because the asterisk is interpreted as "match any sequence of characters."

The solution: The tilde escape character

To force Excel to treat the asterisk as a literal character rather than a wildcard, you must add a tilde to the formula:

=COUNTIF(A:A,"*~*")

Here, the tilde escapes the second asterisk, treating it as a literal character. The first asterisk is a wildcard, ensuring the function finds cells containing any number of characters before the asterisk.

The formula now correctly returns 6, reflecting the count of products marked with the asterisk for clearance. This demonstrates the power of the tilde in turning an imprecise wildcard search into a precise literal character match.

Related

The Ultimate Glossary of Microsoft Excel Symbols

You don’t truly understand Excel until you know its symbols.

Using the tilde in criteria-based functions

Beyond COUNTIF, the tilde is essential when you use any criteria-based function to search for asterisks and question marks.

Using the same inventory data as before, suppose items flagged with a question mark require special accounting.

Specifically, you want to sum the sales values of all these items. Since the question mark is interpreted as a wildcard character by default, you need to use the escape tilde directly before it in your SUMIF formula:

=SUMIF(A:A,"*~?",C:C)

Here, the asterisk is used as a wildcard character because it isn’t preceded by a tilde. This means that it matches any sequence of characters at the beginning of the item code. However, the tilde-question mark sequence is interpreted as a literal question mark.

As a result, the entire criteria tells Excel to sum the values in column C for any item code in column A that ends with the literal question mark.

Finding and replacing asterisks and question marks

Excel’s Find and Replace tool (Ctrl+H) also recognizes wildcards. Just as in formulas, if you want to target a literal asterisk or question mark, you must use the tilde to escape the wildcard function.

Imagine you have completed the special accounting in the example above, and you now want to remove the question marks.

Since the question marks are only part of the character strings rather than the entire cell contents, make sure "Match entire cell contents" is unchecked in the Find and Replace dialog.

Typing ? or *? into the Find What field and leaving the Replace With field blank replaces all the contents of all the cells with blanks (in other words, you’ll get a whole data loss).

The only safe way to target only the literal question mark is by using the tilde (~?), and leaving the Replace With criteria blank tells Excel to replace this combination with blanks, which is the same as a deletion.

As a result, when you click "Replace All," all the string-end question marks are successfully removed.

Related

Filtering data with asterisks and question marks

When you use Excel’s built-in filtering tools, the searches also recognize asterisks and question marks as wildcards. Using the tilde is the only way to isolate data containing the literal characters.

Suppose you want to filter all the items in your inventory so that you only see the items containing an asterisk.

Select any cell in the dataset and click "Filter" in the Data bar to add filter buttons. Alternatively, format the range as an Excel table to add filter buttons automatically.

If you type * into the filter search field, or click Text Filters > Contains and type *, Excel will return *all *the items, including those without an asterisk, as the asterisk is considered a wildcard representing any sequence of characters.

So, to instruct Excel to find only the literal asterisk, type the escape character directly within the filter criteria.

This successfully isolates the items containing an asterisk.

Escaping the escape character: Finding the tilde itself

So, the asterisk and question marks are the wildcards, and the tilde is the escape character. But what do you do when the literal character you need to find or count is the tilde itself? The principle remains exactly the same: you must escape the character’s special function by typing two tildes.

This sequence works identically across all three features I’ve covered:

• Formulas: To count cells containing the literal tilde, use =COUNTIF(A:A,"*~~*").
• Find and Replace: In the Find What field, type ~~ to find every literal tilde in your data.
• Filtering: In the search, type ~~, or click Text Filters > Contains and enter ~~ to isolate records containing the tilde.

---

The tilde isn’t the only sign you need to understand to truly get your head around Excel’s inner workings. For example, the at (@) sign returns a single row value instead of spilling an array, the hash (#) sign allows formulas to adjust to changes in spilled range sizes, and the double-unary operator (–) converts Boolean values to numeric ones.

Microsoft 365 Personal

OS Windows, macOS, iPhone, iPad, Android

Free trial 1 month

Microsoft 365 includes access to Office apps like Word, Excel, and PowerPoint on up to five devices, 1 TB of OneDrive storage, and more.