How to find, replace and remove asterisks in Excel

When processing text in Excel, you often need to remove punctuation and other unwanted characters. Usually it’s done with Find and Replace: a universal procedure that finds, replaces, and if you replace with nothing, removes characters from cells.

But when you try to remove the asterisk (*) this way, something like this happens:

Instead of removing the asterisks, Excel clears all the cells. What’s going on?

Why Excel deletes everything

The asterisk is a wildcard character. In Find and Replace, “*” means “any number of any characters”, so the search for “*” matches the entire content of every cell, and replacing it with nothing clears them. The question mark (?) is a wildcard too: it means “any single character”. More about them in the article on wildcard characters in Excel.

How to find, replace or remove the asterisk

To make Excel treat the asterisk as a regular character, escape it with a tilde (~). The tilde key is usually under Esc, typed with Shift.

• Press Ctrl+H.
• In Find what, type ~*.
• Leave Replace with empty to remove the asterisks, or type the replacement.
• Click Replace All.

The same way, ~? finds the question mark, and ~~ the tilde itself. And now you can guess how to just find the asterisks: the same ~* in the Find tab (Ctrl+F) :)

The asterisk in formulas

Formulas behave differently depending on the function. SUBSTITUTE doesn’t know about wildcards, so this formula simply removes all asterisks, “5* hotel” → “5 hotel”:

=SUBSTITUTE(A1,"*","")

Functions that support wildcards, such as COUNTIF, SUMIF, VLOOKUP, MATCH and SEARCH, need the tilde. Count the cells that contain an asterisk:

=COUNTIF(A1:A5,"*~**")

Here the first and last “*” are wildcards (anything before and after), and “~*” in the middle is the asterisk itself. Without the tilde, =COUNTIF(A1:A5,”*”) counts all non-empty text cells. Check whether a cell contains an asterisk:

=ISNUMBER(SEARCH("~*",A1))

Or use FIND, which doesn’t support wildcards at all: =FIND(“*”,A1) returns the position of the first asterisk without any tilde.

Remove asterisks with !SEMTools

In !SEMTools, the procedures to remove characters don’t use wildcards, so you don’t need to remember the tilde: select the range and remove the asterisk, or all punctuation at once, right in place. And to find out which cells contain an asterisk, there are the procedures to find characters in Excel.

Summary

I hope you found what you were looking for on this page. In Find and Replace and in functions with wildcards, put a tilde before the asterisk: ~*. SUBSTITUTE and FIND take the asterisk literally.

See also:

• Extract characters from cells by condition
• Remove extra spaces in Excel