When working with Excel, you might encounter situations where special characters such as the asterisk (*) behave unexpectedly. This is because Excel treats certain symbols as wildcards or operators rather than plain text. In this blog, we will explore how to escape characters in Excel to ensure they are interpreted correctly.
Why Do We Need to Escape Characters in Excel?
Excel uses some characters, including *, ?, and ~, as wildcards in functions like SEARCH, MATCH, and FILTER. For example:
*(asterisk) represents any number of characters.?(question mark) represents a single character.~(tilde) is used as an escape character to treat the wildcard characters as literals.
If you need Excel to treat * as a regular character instead of a wildcard, you must escape it properly.
How to Escape * in Excel
1. Using the Tilde (~) for Escape
The tilde (~) is used to escape special characters in Excel formulas. If you need to find or compare a literal asterisk, prefix it with a tilde (~). For example:
=SEARCH("~*", A1)This formula searches for * in cell A1 as a literal character instead of a wildcard.
2. Using Quotation Marks in Formulas
When working with text functions, enclose special characters within double quotes. However, if using functions like SUBSTITUTE, you don’t need escaping:
=SUBSTITUTE(A1, "*", "#")This formula replaces all * characters in A1 with #.
3. Formatting Cells as Text
If you want Excel to treat an asterisk as text rather than a wildcard:
- Select the cells where you will enter data.
- Right-click and choose Format Cells.
- Select Text and click OK.
- Now, when you type
*, Excel will treat it as text.
4. Using Apostrophe (') Before Special Characters
If you need to input * in a cell without triggering wildcard behavior, start the entry with an apostrophe ('). For example:
'*This ensures Excel treats it as plain text.
Techy Chalkboard in bits and pieces 