Remove Asterisk (*) in Excel

Remove asterisk (*) symbol In Excel is a little tricky as it’s a wildcard character and you cannot just use fine and replace to remove an ace risk symbol.

But nothing to worry about – I’ll tell you a simple methods to quickly remove all the asterisk symbols from your data set using the Find and Replace method and a simple formula.

So let’s get to it!

Remove Asterisk Using Find and Replace

Let’s start with the Find and Replace method.

Below I have a data set of names where some of the names have an asterisk (*) symbol at the end, and I want to remove these asterisk symbols.

Here are the steps to do this using Find and Replace:

1. Select all the cells from which you want to remove the asterisk symbol
2. Click the Home tab.

3. In the editing group click on the Find and Select icon
4. In the drop-down menu that shows up, click on the Replace option. This will open the find and replace dialog box with the Replace tab activated

Note: You can also use the keyboard shortcut Control + H to open the find and replace dialog box

5. In the Find what field, enter ~* (~ followed by these * symbol)

5. Leave the Replace with field empty
6. Click on Replace All.

That’s it!

Excel would remove all the asterisk characters and show you a dialog box telling you how many replacements have been made.

When we add the ~ key before the * symbol, it tells Excel to treat the asterisk symbol just like any other character (and not a wild card character)

Also read: Remove Dashes/Hyphens in Excel

Remove Asterisk Using SUBSTITUTE Formula

You can also use the SUBSTITUTE function to quickly remove all the asterisk symbols in a cell in Excel.

Below I have the same data set where I have names in column A and I want to remove the asterisk signs from all of these cells.

You can do this by using the below formula:

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

Enter this formula in cell B two and then copy it for all the other cells in the column.

And if you have dynamic arrays in your Excel version you can also use the below formula (where you do not need to copy it for the other cells as it will spill the results and fill the column automatically).

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

The above formulas would remove all the asterisk symbols in the cells.

If you want to remove specific instances of the asterisk symbols, you can use the third argument of the SUBSTITUTE function. For example the below formula would only remove the first instance of the * symbol in the cell:

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

Also read: Remove Parentheses in Excel

VBA to Remove Asterisk Symbols from a Range of Selected Cells

And finally if you also use a VBA macro code to remove the asterisk symbol from a range of selected cells or even the entire worksheet.

Sub RemoveAsterisks()
    Dim cell As Range
    For Each cell In Selection
        cell.Value = Replace(cell.Value, "*", "")
    Next cell
End Sub

To use this above VBA code:

1. Select all the cells from which you want to remove the * symbol
2. Hold the ALT key and press the F11 key to open the VBA editor in Excel.
3. Go to Insert option in the menu and click on Module. This will insert a new module for the workbook.
4. Copy and paste the above code into the module.
5. Place the cursor anywhere in the macro code and press the F5 key to run the macro.

As soon as you run the macro code, it would go through each cell in the selection and remove any ace risk symbol it finds.

Remember that the changes done by VBA macro codes are irreversible, so it’s always a good idea to create a backup copy of your data set before running the macro

In this article, I showed you three different methods you can use to remove asterisk symbols from cells in Excel.

I hope you found this article helpful. If you have any questions or suggestions for me, please let me know in the comments section below.

Other Excel articles you may also like: