How to Remove Asterisk in Excel (5 Simple Methods)

Get FREE Advanced Excel Exercises with Solutions!

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 placed in a different position.

Remove Asterisk in Excel

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.

Use Find & Replace Command to Remove Asterisk in Excel

  • In the “Find what” section type 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.
  • Press OK.

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

Use Find & Replace Command to Remove Asterisk in Excel

Read More: How to Find And Replace Values Using Wildcards 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-
=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 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.

Perform SUBSTITUTE Function to Remove Asterisk 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.

Combine TRUNC & SUBSTITUTE Functions to Remove Asterisk from Numbers only

Steps:

  • Select a cell. Here I have selected cell (F5).
  • Apply the following formula-
=TRUNC(SUBSTITUTE(D5,"*",""),0)

Where,

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

Combine TRUNC & SUBSTITUTE Functions to Remove Asterisk from Numbers only

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

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

Combine TRUNC & SUBSTITUTE Functions to Remove Asterisk from Numbers only


Similar Readings


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-
=SUMPRODUCT(SUBSTITUTE(0&D5,"*","")+0)

Utilize SUMPRODUCT & SUBSTITUTE Functions to Remove Asterisk from Numbers only

  • 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.

Utilize SUMPRODUCT & SUBSTITUTE Functions to Remove Asterisk from Numbers only


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.

Run a VBA Code to Remove Asterisk in Excel

  • 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

Run a VBA Code to Remove Asterisk in Excel

  • 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. We, the Exceldemy team, are always responsive to your queries. Stay tuned and keep learning.


Related Articles

Wasim Akram
Wasim Akram

Hi! my name is Wasim Akram. I am a graduate in mechanical engineering from Ahsanullah University of Science & Technology. I am passionate about learning new things and writing articles. I write article about Microsoft Excel and it's my favourite thing to do.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo