How to Remove Asterisk in Excel (5 Simple Methods)

While working in Microsoft Excel, you might see some not useful asterisk(*) in different cells. This may create problems while editing a dataset or applying formulas. Because formulas will not find the appropriate numeric data or text to calculate. For this reason, you might feel the need to remove those asterisk(*) signs. In this article, I am going to share with you some easy tricks to remove asterisk(*) in Excel.

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

In the following section, I have shared 5 simple and quick methods to remove asterisk(*) in Excel. Stay tuned!

1. Using Find & Replace Command to Remove Asterisk in Excel

You might find asterisk(*) signs in different positions of a cell or in different columns or rows. This becomes difficult to remove. But no worries. In this method, I am explaining an easy technique to remove an asterisk using the “Find & Replace” command.

Steps:

• While in the dataset, press Ctrl+H to open the “Find & Replace” window.

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

• A confirmation window will appear confirming the replacement.
• Press OK.

• Here we have our new dataset, removing all the asterisks from the worksheet.

Read More: How to Remove Apostrophe in Excel

2. Inserting SUBSTITUTE Function to Remove Asterisk in Excel

The SUBSTITUTE function in Excel replaces a specific text from a given string. To remove the asterisk, you can use the substitute function too.

Steps:

• Choose a cell to apply the formula. Here I have chosen cell (G5).
• Put the formula down-

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

• As you can see, we have successfully removed all the asterisk(*) from the column, where the asterisk is placed in a different position.

Read More: How to Remove Non-Alphanumeric Characters in Excel

3. Combining TRUNC and SUBSTITUTE Functions to Remove Asterisk from Numbers Only

In those previous methods, we have removed asterisks from texts. Now I will show you some quick ways to remove asterisk(*) from numbers. Asterisk(*) may stay in different places of a number. But don’t worry. With the combination of the TRUNC and SUBSTITUTE functions, you can remove asterisks very easily from numbers.

Suppose we have a dataset of some Employee Name & their Employee ID. In the Employee ID column, you will see various asterisk signs. Now we will remove those with the help of functions.

Steps:

• Select a cell. Here I have selected cell (F5).
• Apply the following formula-

Where,

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

• Hit the Enter.
• Drag the “fill handle” down.

• Thus, we can remove all the asterisks from numbers. Simple, isn’t it?

4. Joining SUMPRODUCT and SUBSTITUTE Functions to Remove Asterisk from Numbers

In this method, I am sharing another way to remove asterisk from numbers using functions. Here the SUBSTITUTE function will substitute the asterisk sign from the cells and then the SUMPRODUCT function will return the total values of a cell.

Steps:

• To apply the formula, choose a cell. Here I have chosen cell (F5).
• Write the formula down-

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

• The expected precious result is in our hands now.
• The asterisk sign may stay between numbers, starting at a number or at the end of numbers. These combined functions will do the work in Excel.

5. Applying Excel VBA Code for Removing Asterisk

You can remove the asterisk from your Excel workbook by running a simple VBA code. Follow my steps below to learn-

Steps:

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

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

• Place the following code in the module and click “Run”.
• The code is-

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

• Here in the output msgbox you will get the demanded results, removing all the asterisks from the dataset.

Things to Remember

• While using the “Find & Replace” window, 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

Download this practice workbook to exercise while you are reading this article.

Conclusion

In this article, I have tried to cover all the simple methods to remove asterisk in Excel. Take a tour of the practice workbook and download the file to practice by yourself. Hope you find it useful. Please inform us in the comment section about your experience.

Related Articles

• How to Remove Parentheses in Excel
• How to Remove Semicolon in Excel
• How to Remove Dashes in Excel
• How to Remove Dashes from Phone Number in Excel
• How to Remove Dashes from SSN in Excel
• How to Remove Blank Characters in Excel
• How to Remove Non-Printable Characters in Excel 

<< Go Back To Remove Specific Characters in Excel | Excel Remove Characters | Data Cleaning in Excel | Learn Excel