How to Remove Asterisk in Excel (5 Simple Methods)

Suppose we have a dataset of a fruit shop containing columns for Product, Product Code, & Total Sales. We will remove the asterisk sign from the Product Code column and place it in a different position.

Remove Asterisk in Excel

Method 1 – Using the Find & Replace Command

Steps:

Select any cell in the dataset and press Ctrl+H to open the “Find & Replace” window.

Use Find & Replace Command to Remove Asterisk in Excel

In the “Find what” section, type the tilde & asterisk sign (~*).
Click “Replace All” to continue.

Use Find & Replace Command to Remove Asterisk in Excel

A confirmation window will appear confirming the replacement.

Click OK.

All the asterisks from the worksheet are removed.

Use Find & Replace Command to Remove Asterisk in Excel

Read More: How to Remove Apostrophe in Excel

Method 2 – Using the SUBSTITUTE Function

The SUBSTITUTE function replaces a specific text from a given string.

Steps:

Choose a cell to apply the formula, here cell G5.
Enter the following formula:

=SUBSTITUTE(D5,"*","")

Perform SUBSTITUTE Function to Remove Asterisk in Excel

Press Enter to get the output in the cell.
Pull the “Fill Handle” down to fill up the cells below.

All the asterisks (*) are removed from the column, even when they are in different positions.

Perform SUBSTITUTE Function to Remove Asterisk in Excel

Method 3 – Combining the TRUNC and SUBSTITUTE Functions to Remove Asterisk from Numbers Only

In those previous methods, we have removed asterisks from texts. Now we’ll remove the asterisk (*) from numbers.

Suppose we have a dataset of some Employee Names & IDs. In the Employee ID column are various asterisks. Let’s remove those with the help of functions.

Combine TRUNC & SUBSTITUTE Functions to Remove Asterisk from Numbers only

Steps:

In cell F5, enter the following formula:

=TRUNC(SUBSTITUTE(D5,"*",""),0)

The TRUNC function returns a truncated number based on the given number of digits.

Combine TRUNC & SUBSTITUTE Functions to Remove Asterisk from Numbers only

Press Enter.
Drag the “Fill Handle” down.

All the asterisks are removed from the numbers.

Combine TRUNC & SUBSTITUTE Functions to Remove Asterisk from Numbers only

Method 4 – Using SUMPRODUCT and SUBSTITUTE Functions to Remove Asterisk from Numbers

An alternative method to remove asterisks from numbers is with the SUBSTITUTE function, which will substitute the asterisk sign from the cells, and then the SUMPRODUCT function which will return the total values of a cell.

Steps:

In cell F5, enter the following formula:

=SUMPRODUCT(SUBSTITUTE(0&D5,"*","")+0)

Utilize SUMPRODUCT & SUBSTITUTE Functions to Remove Asterisk from Numbers only

Press Enter.
Pull the “Fill Handle” down to get the result in all cells.

Regardless of where they were positioned in the numbers, the asterisks are removed.

Utilize SUMPRODUCT & SUBSTITUTE Functions to Remove Asterisk from Numbers only

Method 5 – Using Excel VBA Code

Steps:

Press Alt+F11 to open the “Microsoft Visual Basic for Applications” window.

Run a VBA Code to Remove Asterisk in Excel

Go to “Module” from the “Insert” option.

Place the following code in the module that opens, then click “Run”:

Sub Find_andReplace()
Dim stringPC As String
stringPC = "OE*12, *AE 45, WN 94*, SY*23**, **GE 13**, MG****94, **BN-56**, **PA**61**"
stringPC = Replace(stringPC, "", " ")
MsgBox stringPC
End Sub

Run a VBA Code to Remove Asterisk in Excel

In the output MsgBox are the requested results, with all the asterisks from the dataset removed.

Things to Remember

When using the “Find & Replace” feature, don’t forget to put the tilde(~) sign before the asterisk sign in the “Find what” section. Otherwise, you will lose all the dataset texts and numbers from the worksheet.

Download Practice Workbook

Remove Asterisks.xlsm