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.
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 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 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,"*","")
- 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.
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-
=TRUNC(SUBSTITUTE(D5,"*",""),0)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?
Similar Readings
- How to Use Wildcards in Excel?
- Search for Question Mark in Excel (4 Suitable Methods)
- Excel Match Wildcard in Lookup Array (with 3 Formulas)
- How to Use COUNTIF with Wildcard in Excel (7 Easy Ways)
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)
- 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. We, the Exceldemy team, are always responsive to your queries. Stay tuned and keep learning.
