Remove asterisks in Excel without changing your formulas

An asterisk attached to a product name might mark a revised entry, a provisional price, or something else entirely. Removing the mark can make a list easier to compare, but it should leave the name intact. Excel's Find and Replace treats an unescaped asterisk as a search pattern, so the match can extend across the whole entry.

Keep the original file and establish what the marker means before cleaning it up. Removing a symbol from a provisional amount does not make that amount final. In desktop Excel for Windows, a plain text list can be edited with Find and Replace. A helper column is a better fit when you need to retain formulas and compare the results beside their source.

Remove the symbol from a text list

Enter ~* in Find what. The tilde makes the following asterisk a literal character. The equivalent searches are ~? for a question mark and ~~ for a tilde. Without that escape, an asterisk can represent a string of characters; a question mark represents one character. These are Excel's wildcard rules.

If the wrong replacement has just removed your entries, use Ctrl+Z before continuing. Undo is the immediate recovery step, not a guarantee that changes can be recovered after the undo history is unavailable. Check that the original entries have returned before making a working copy.

For a column containing ordinary text rather than formulas:

1. Select the cells to clean and press Ctrl+H.
2. Set Find what to ~*. Leave Replace with completely empty, including spaces.
3. Expand Options, set Within to Sheet, and turn off Match entire cell contents. Check for an unwanted formatting condition.
4. Use Find Next and then Replace once. A sample entry such as Filter* should become Filter. Apply Replace All only after confirming the intended cells and result.

A space in the replacement field leaves a space behind. That may look clean while still preventing a later exact comparison with another product list. If you want a different marker, enter it explicitly instead of leaving the field blank.

Excel's Find All results let you inspect matching cells. If notes or unrelated columns appear, stop and narrow the task. Copying only the list to a separate working sheet is another way to keep the cleanup away from the rest of the report.

Find one complete code

A code that contains an asterisk may need to stay exactly as supplied. To find P-17*, use P-17~* and enable Match entire cell contents. Escaping the symbol fixes its meaning; the checkbox requires the match to cover the whole cell.

Checking the box alone does not turn a wildcard into a literal symbol. A Microsoft Q&A question about database codes describes that exact difficulty: two entries kept appearing even after whole-cell matching was enabled. The missing tilde was still allowing a broader match.

Turn whole-cell matching off again when removing a symbol from inside longer names. Searching for ~* with that box checked targets a cell containing only an asterisk, not an entry such as Filter*. Quoting the search text is not a substitute for escaping the wildcard.

Keep multiplication signs out of the cleanup

The corrected search is not formula protection. In =3*4, the asterisk performs multiplication. Removing it produces =34: a different number, with no error required. This example follows Excel's arithmetic operator rules. A cleanup can therefore damage a calculation without making the worksheet look obviously broken.

In Windows Excel, Find can search Values, but the Replace tab uses Formulas. A match in a formula can involve something you never saw in the cell's displayed result. Inspect the formula bar before treating a mixed column as plain text.

To locate formulas in an unfamiliar area, select that area and use Ctrl+G → Special → Formulas. Microsoft's Go To Special instructions explain this selection. Use it to see where calculations sit, rather than assuming an entire imported report contains constants.

The formula bar can also reveal that a visible asterisk is only formatting. If the cell shows 1200* but the bar contains just the number 1200, inspect its custom number format. A format such as 0"*" adds the symbol without storing it in the value. Open the Number dialog from the Home tab, select Custom, and inspect the code. In this example, changing 0"*" to 0 removes the display marker while preserving the number.

For example, a product-name column might include formulas that retrieve names from another sheet. Keep those formulas intact and create cleaned names beside them. There is no need to edit the multiplication in a neighboring amount column, or the expression that supplies the original name.

Compare the cleaned text beside its source

Leave column A as the source. In an empty column B, add a heading such as “Cleaned name,” then enter =SUBSTITUTE(A2,"*","") in B2 and fill down to the last required row. Here the search string is "*", not the ~* used in the dialog.

SUBSTITUTE replaces the specified text within a string. The helper formula reads A2's value and returns cleaned text; it does not rewrite A2's formula. That gives you an original and a result to compare on the same row.

The three-argument formula removes every occurrence. To remove only the first, use =SUBSTITUTE(A2,"*","",1). Applied to the provisional example, it leaves Filterprovisional*. The final argument identifies an occurrence from the left, not “the final character.”

Review entries with multiple markers before applying a single rule to the whole column. If Filter* and Filter become the same name, that does not establish that they are duplicate records. Deciding whether to merge them requires knowing what the marker meant in the supplied list.

Treat amounts and identifiers differently

SUBSTITUTE returns text, even when the result looks like a number. For an amount column, use =VALUE(SUBSTITUTE(A2,"*","")) to create numeric results. Microsoft's text-to-number guidance describes using VALUE in a neighboring column.

With example inputs 1200* and 800*, the intended numeric results are 1200 and 800, totaling 2000. Compare that total with the source amounts. Removing the visible marker and producing usable numbers are two different checks.

Pasting a SUBSTITUTE result as a value does not convert its text into a number. If the recipient needs to total the amounts, check the VALUE results before copying those results into the delivery sheet. For an identifier such as 00120, copy the text result instead so the leading zeros remain part of the code.

An entry such as Pending* still is not a number after cleanup. VALUE returns an error when the text is not in a recognized format. Do not hide that case by turning every error into zero: an unknown price is not a zero price. Keep the source entry available for clarification.

Identifiers need different treatment. Cleaning 00120* should leave the code 00120, not the number 120. Keep the SUBSTITUTE text result for codes. Excel's guidance on storing numbers as text explains why identifiers and amounts should not share an automatic numeric conversion.

Deliver values without losing your working copy

A helper column remains useful while you maintain the list: changes to the source feed its results. When someone needs a fixed copy of the cleaned entries, copy those results into a delivery sheet using Home → Paste → Values.

Replacing formulas with their results removes the dependency at the destination. The recipient does not need your source column, but later corrections to that source will not update the delivered values. Keep the original and helper formulas in your working file.

Compare a few delivered rows with the original: one with a single marker, one with several, and one with none. Check leading zeros in codes and unresolved entries in amount columns. The finished list should still identify the same items and support the intended calculations after the markers have gone.