Special characters in Excel

Everyone means something different by “special characters in Excel”. Different groups of characters have different special properties, and that’s why users call them special. Below I list all such characters, what they do and how to deal with them.

If you’re looking for symbols and icons to insert into cells, you need the CHAR function, and the UNICHAR function lets you insert even more: arrows, check marks, stars and thousands of other Unicode characters.

Wildcard characters in Excel

Of all the special characters in Excel, the asterisk (*) and the question mark (?) probably raise the most questions among beginners. Yet they are very useful. They are wildcards: they stand in a string instead of other characters, only marking them in a certain way.

How exactly? It’s simple:

• the question mark (?) means any single character: a digit, a letter, a space or something more exotic, like a non-breaking space or a tab;
• the asterisk (*) means any number of any characters, including none.

Using wildcards in Find and Replace

Wildcards are most useful in the built-in Find and Replace. Some examples:

• “?” with Match entire cell contents finds all cells with exactly one character, for example one-digit numbers;
• “?7” finds two-character cells ending with 7;
• “7*” finds all cells starting with 7;
• “*” finds all non-empty cells.

Note the Match entire cell contents checkbox. Thanks to it, the search doesn’t look inside the cell, but compares the pattern with the whole cell.

How to find the asterisk (*) and the question mark

The asterisk and the question mark can also appear in the text itself, and you may need to find them. Then you have to tell Excel to treat them literally, not as wildcards.

Microsoft has provided for this: put a tilde (~) before them. The tilde is usually in the top left corner of the keyboard, under Esc. So “~*” finds an asterisk, and “~?” finds a question mark. More in the article on how to find and remove asterisks in Excel.

Funnily enough, the tilde itself also has to be escaped with a tilde to be found: “~~”. Otherwise Excel won’t find it.

Wildcards in filters and the Advanced Filter

Just like in Find and Replace, you can use wildcards when filtering data: Text Filters → Contains, Begins With and so on, and in the criteria range of the Advanced Filter. Escaping with the tilde works here too.

Wildcards in formulas

Besides the built-in tools, wildcards work in some formulas:

• COUNTIF and similar conditional functions: COUNTIFS, SUMIF, SUMIFS, AVERAGEIF;
• SEARCH (but not FIND, which takes characters literally);
• VLOOKUP and similar lookup functions: HLOOKUP, MATCH, and XLOOKUP with match mode 2.

For example, this formula counts the cells in A1:A100 that contain the word “sale” anywhere in the text:

=COUNTIF(A1:A100,"*sale*")

Math symbols at the beginning of a cell

Most of you have probably seen this: if you type text that starts with a plus, an equal sign or a minus, Excel usually returns the #NAME? error.

How do you put an equal sign, a minus or a plus at the beginning of a cell? The problem is that the equal sign starts every formula, and plus and minus are math operators that Excel expects to work with numbers.

For these characters to be treated as text, put any other character before them. Another option is to type an apostrophe (‘) before them. That’s another special Excel character, which makes the cell value text. With it, you explicitly tell Excel that the value is just text and nothing else. Excel itself suggests this option when you type such a value manually.

The apostrophe (‘) in Excel

The apostrophe was already mentioned above, where it helped to put an equal sign at the beginning of a cell without turning it into a formula.

Typed at the beginning of a cell, it tells Excel that the data is strictly text. A curious fact: the length of the cell content can change in some cases. For example, if you type 50%, Excel stores the number 0.5, and if you type ‘50%, it stores the text “50%”. The same goes for dates: without the apostrophe, they become serial numbers. More about this in the article on the TEXT function.

The apostrophe itself is invisible to everything else. The length of the cell stays the same as without it, and formulas referring to the cell don’t see it. In other words, it doesn’t harm the data, it only makes it text. That’s also why it’s a common reason why numbers are saved as text after an import.

The ampersand (&) in Excel

In formulas, the ampersand is the concatenation operator, a shorter replacement for the CONCATENATE function. Both formulas below do exactly the same thing, joining cell A1, a space and cell B1:

=CONCATENATE(A1," ",B1)

=A1&" "&B1

Obviously, the second one is shorter and has no nesting (no parentheses), which is sometimes useful in complex formulas.

One more property: concatenation is a text operation by nature, so even if you join two numbers with an ampersand, the result is text: =1&2 returns the text “12”, not the number 12.

Other characters with a special meaning

• $, the dollar sign, makes a cell reference absolute: $A$1 doesn’t change when you copy the formula, $A1 fixes only the column, A$1 only the row. Press F4 to cycle through the options.
• #, after a cell reference (A1#), refers to the whole spill range of a dynamic array in Excel 365. More about this in the article on array formulas.
• @ before a reference or function in Excel 365 means implicit intersection: take one value instead of a whole array. It often appears automatically in formulas from older workbooks.
• Invisible characters: the non-breaking space CHAR(160) and the line break CHAR(10). They look like an ordinary space or nothing at all, but break searches and comparisons. See how to remove extra spaces.

Summary

I hope you found what you were looking for on this page and now know how special characters work in Excel. Most problems come from the asterisk and the question mark in searches (escape them with a tilde) and from text that starts with an equal sign or a minus (put an apostrophe before it).

Related articles: how to find certain characters in Excel cells, how to remove extra characters from all cells and how to add characters to every cell of a range.