You need to know what the tilde (~) does in Excel
Imagine this: you need to find or count cells containing asterisks.
or question marks (?) in Excel, but you get incorrect results. This is because asterisks and question marks are wildcards. The solution? Excel’s essential escape character, the tilde (~).
Wildcards and escapes in Excel
Wildcards are symbols that allow you to create flexible search criteria in Excel functions such as COUNTIF or SUMIF, as well as features such as Find & Replace and Filter. However, to use them accurately, you need to know how to use the escape character (the tilde) to search for the wildcards themselves.
Excel wildcards
|
Excel recognizes two main wildcards: the asterisk and the question mark. |
Wildcard |
Name |
What this represents |
|---|---|---|---|
|
Example |
* |
Asterisk |
Any sequence of characters (including no characters). |
|
Searching for “A*” matches “Apple,” “A,” and “Accountant.” |
? |
Question mark |
Any character in this position. |
Searching for “b?t” matches “bat”, “bet”, “bit” and “but”, but not “boot”.
The Problem: When Wildcards Hide Literal Characters
If your data contains an actual asterisk or question mark and you try to search for it using a function or feature that supports wildcards, Excel interprets the symbol as its generic meaning, not the literal character.
An Excel database of products, with various items followed by an asterisk.
=COUNTIF(A:A,"*")
You want to know exactly how many products are marked for customs clearance, so in a separate cell you type:
The COUNTIF function with the asterisk wildcard returning the total count of all elements.
The solution: the tilde escape character
=COUNTIF(A:A,"*~*")
To force Excel to treat the asterisk as a literal character rather than a wildcard, you need to add a tilde to the formula:
The COUNTIF function with the asterisk wildcard negated by the tilde.
Related
The Ultimate Glossary of Microsoft Excel Symbols
You don’t really understand Excel until you know its symbols.
Use the tilde in criteria-based functions
Beyond COUNTIF, the tilde is essential when using a criteria-based function to search for asterisks and question marks.
An Excel database of products, with various sections followed by a question mark.
=SUMIF(A:A,"*~?",C:C)
More specifically, you want to add up the sales values of all of these items. Since the question mark is interpreted as a wildcard character by default, you must use the escape tilde right before it in your SUMIF formula:
Here, the asterisk is used as a wildcard because it is not 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.
The SUMIF function with the asterisk wildcard followed by an escaped question mark to sum the sales of all products containing a question mark.
Find and replace asterisks and question marks
Excel’s Find and Replace tool (Ctrl+H) also recognizes wildcards. Just like in formulas, if you want to target a literal asterisk or question mark, you must use the tilde to escape the wildcard function.
Imagine that you have completed the special accounting in the example above and now want to remove the question marks.
Match entire cell contents in Excel’s Find and Replace dialog box is empty. Typing ? Or *?
in the Find field and leaving the Replace with blank field replaces all contents of all cells with spaces (in other words, you will get complete data loss).
Microsoft Excel’s Find and Replace dialog box, with a tilde and marked question typed in the Find field and the Replace with field left blank.
Related
The Best Search Functions in Excel: Examples, Pros and Cons
Choose the best way to find matching values.
Filtering data with asterisks and question marks
When you use Excel’s built-in filtering tools, searches also recognize asterisks and question marks as wildcards. Using the tilde is the only way to isolate data containing literal characters.
Let’s say you want to filter all items in your inventory so that you only see items with an asterisk.
Select any cell in the dataset and click “Filter” in the data bar to add filter buttons. You can also format the range as an Excel table to automatically add filter buttons. If you type * in the filter search field, or click Text Filters > Contains and enter* Excel will return all
An asterisk is entered into the filter search bar in Excel and all items (including those without an asterisk) are returned.
A tilde and an asterisk are entered into the filter search bar in Excel and all items containing an asterisk are returned.
This successfully isolates elements 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 to 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 on all three features I covered: Formulas:
- To count cells containing the literal tilde, use =COUNTIF(A:A,”*~~*”). Find and replace: In the Search field, enter ~~
- to find every literal tilde in your data. Filtration: 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 understand the inner workings of Excel. For example, the at sign (@) returns a single row value instead of scattering an array, the pound sign (#) allows formulas to adjust to changes in distributed range sizes, and the double-unary operator (–) converts Boolean values to numeric values.
-
Operating system
- 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, 1TB of OneDrive storage, and more.
