Excel Find and Replace Asterisk Characters

To find a literal asterisk in Excel for Microsoft 365 on Windows, press Ctrl+H, enter ~* in Find what, enter the replacement in Replace with, then choose Replace or Replace All. The tilde tells Excel to treat the asterisk as a character, not as a wildcard that matches other text.

Why the asterisk needs an escape character

Excel uses certain characters as wildcards in Find and Replace. An asterisk normally stands for any sequence of characters, including a sequence with no characters in it. Searching for * by itself can therefore match far more than cells containing an actual star: it can match text of many different lengths. That is why a search for an asterisk may seem to find nearly everything.

Put a tilde directly before the asterisk to change its meaning. In the Find what box, ~* means “look for the actual asterisk character.” The tilde is an escape character; it is an instruction to interpret the next wildcard character literally. It does not mean that the cell must contain only an asterisk. Unless you turn on Match entire cell contents, Excel can find the star within a longer entry such as AB*47.

The same rule helps with the other Excel wildcard characters: use ~? to search for a literal question mark, and ~~ to search for a literal tilde. A question mark normally represents one character, while a tilde is the escape character itself. These distinctions are useful when cleaning imported data, product codes, notes, or text copied from another system.

Replace asterisks with the ribbon or shortcut

Select the cells you want to edit, if you want to limit the operation to a particular range. If nothing is selected, Excel searches the current worksheet.
On Windows, press Ctrl+H to open Find and Replace. You can also choose Home > Find & Select > Replace.
In Find what, type ~*. In Replace with, type the character or text that should take the star’s place. Leave Replace with empty if the goal is to remove the asterisks.
Choose Find Next first if you want to inspect matches one at a time. Choose Replace to change the current match, or Replace All to change every match in the selected search scope.
Read Excel’s confirmation message after Replace All. It reports how many replacements were made; check a few affected cells before continuing with other edits.

On Mac, use the Replace tab in Excel’s Find and Replace dialog or the Home ribbon’s Find & Select > Replace command. Keyboard shortcuts can differ with the keyboard and Excel settings, so the ribbon route is a dependable alternative. In Excel for the web, open Find and Replace from the Home ribbon; the precise dialog layout can differ slightly from the Windows desktop version, but the literal search pattern remains ~*.

Examples: remove, change, or locate a star

Suppose cells contain codes such as PR*100, PR*205, and PR*310. To remove the star while keeping the rest of each code, search for ~* and leave Replace with blank. The results become PR100, PR205, and PR310. To use a hyphen instead, enter a hyphen in Replace with; the results become PR-100, PR-205, and PR-310.

To locate rather than change a star, press Ctrl+F on Windows and search for ~*. Use Find Next to step through occurrences, or Find All to list the matching cells in the dialog. Finding is useful before replacing when you are unsure whether the mark appears in headings, formulas, or only in the data you intend to clean. The star remains unchanged unless you use a replace command.

Replace with is different from Find what: type the desired output directly in that box. For example, to turn every literal star into the word “star,” search for ~* and enter star in Replace with. Excel substitutes the matching character; it does not require a tilde before the replacement text.

Control where Excel searches

Before using Replace All, check the search scope. A selected range can limit the operation to those cells; otherwise, Excel generally searches the active sheet. In the Find and Replace dialog, expand Options when needed to review settings such as Within, Search, and Look in. Within lets you choose a sheet or workbook when that choice is available. Searching the workbook can affect matching entries on multiple sheets, so use it only when that wider scope is intended.

Look in matters because a visible cell value can differ from what is stored in the cell. For example, a cell may display a result produced by a formula, while the formula itself contains text. If Excel is looking in formulas, a search may find characters in formula text; if it is looking in values, it searches displayed results instead. The available choices can vary by dialog and Excel version. For a routine cleanup of typed text, confirm that you are searching the intended content rather than formula expressions.

Other options affect which cells count as a match. Match case distinguishes uppercase from lowercase letters, although it usually has no practical effect for the asterisk character itself. Match entire cell contents restricts the search to cells whose complete content is the searched pattern. Leave it off to find a star inside longer text. If it is on, a cell containing PR*100 will not match a search for only ~*, because the whole cell is not just an asterisk.

Use a formula when you want to keep the original

Find and Replace edits cells directly. If you want to preserve the source column and produce cleaned text in another column, use SUBSTITUTE instead. Unlike the Find what box, a formula’s text argument treats an asterisk inside quotation marks as an ordinary character; no tilde is needed.

With the original text in A2, enter this in B2 to remove every asterisk from that cell:

=SUBSTITUTE(A2,"*","")

For example, if A2 contains INV*204*OLD, the result in B2 is INV204OLD. Fill the formula down to process more rows, and keep column A unchanged as the original data.

With the original text in A2, use this formula in B2 to replace each asterisk with a hyphen:

=SUBSTITUTE(A2,"*","-")

For the same A2 value, INV*204*OLD, the result is INV-204-OLD. SUBSTITUTE replaces each occurrence of the specified text by default. If you later need fixed text rather than formulas, copy the result cells and use Paste Special > Values. Formulas are especially useful when you need a reviewable, repeatable cleanup or want the original values available for comparison.

Common problems and how to avoid them

Searching for a bare asterisk: Excel reads * as a wildcard. Enter ~* in Find what to match a literal star.
Using the escape character in the wrong place: The tilde belongs before the asterisk in Find what. In Replace with, enter the actual replacement, such as a hyphen, or leave the box empty to delete the match.
Replacing too much: Replace All may affect every matching cell in the chosen scope. Use Find Next and Replace for a cautious, one-at-a-time edit, or select the intended range before replacing.
Getting no results: Check that you typed the tilde and asterisk together as ~*, that the correct sheet or workbook is selected, and that Match entire cell contents is not preventing a partial match.
Changing formulas unexpectedly: Review Look in before replacing. Searching formula text can alter formulas, while searching displayed values targets a different kind of content. Undo immediately if the result is wrong.
Confusing a displayed star with a stored star: A star may be part of a number format or other display behavior rather than the cell’s underlying text. Inspect the formula bar and cell formatting if the search does not find what appears on screen.

Undo, review, and safer cleanup habits

For an ordinary Find and Replace operation, Ctrl+Z on Windows undoes the change, and Command+Z is the usual Mac undo shortcut. Undo promptly if Replace All changed more cells than expected; later workbook actions can make it harder to return to the exact earlier state. If the data is important, save a copy of the workbook before a broad replacement. You can also duplicate the worksheet or copy the source column to a temporary location, then compare the cleaned output against the original.

Start with a small selected range when you are testing the pattern. Verify both a cell that contains a star and a nearby cell that does not. After a workbook-wide replacement, use Find again with ~* to check whether any literal stars remain. If the replacement was meant to remove every star, a remaining result may indicate that the search scope excluded a sheet or that a different character was used in the source data.

Frequently Asked Questions

How do I replace an asterisk with nothing in Excel?
Open Find and Replace, enter ~* in Find what, leave Replace with blank, and choose Replace All. Select the intended range first if you do not want to change the whole active sheet.

Why does Find and Replace match every cell when I search for *?
The asterisk is a wildcard in Excel’s Find what box and can represent any number of characters. Add a tilde before it—~*—to search for the literal character.

Can I replace only asterisks inside longer text?
Yes. Search for ~* and leave Match entire cell contents off. Excel then matches stars within entries such as AB*47, not just cells whose entire contents are a star.

How do I find a literal question mark or tilde?
Use ~? to find a literal question mark and ~~ to find a literal tilde. The tilde escapes the wildcard or tilde that follows it.

Can I remove asterisks with a formula instead?
Yes. If the source text is in A2, =SUBSTITUTE(A2,"*","") returns the text without asterisks in the formula cell. This keeps the original value intact for comparison.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.