Working with data often involves cleaning up.
If your data is imported or copied from an external source, it may contain a number of unwanted characters like commas or asterisks (*).
If you want to remove these asterisk characters in your data or replace them with something else, this tutorial can provide a guideline.
Unlike other characters, replacing or removing the asterisk cannot be done in the traditional ways.
This is because the asterisk character is also used as a wildcard character in Excel (to represent an entire string).
In this tutorial we will show you two ways to remove the asterisk character in Excel.
We will also demonstrate a special case where you would want to replace or remove a certain instance of the asterisk in your text instead of all.
Using the Find and Replace Feature to Replace Asterisks in Excel
Excel’s Find and Replace dialog box is a great way to find items on your worksheet and get things done quickly.
All you need to do is enter your search string to specify what you want to replace and then specify what you want to replace it with.
Now as mentioned before, the asterisk is a wildcard character in Excel. As such, it cannot be treated the same way as other characters.
This means, a simple Find and Replace operation using a ‘*’ in the ‘Find what’ field will not do any good.
Instead, you will need to prefix the asterisk with an escape character like the tilde (~). Microsoft Excel uses the tilde (~) as a marker to indicate that the next character is a literal.
Let us see an example to understand how we can use the Find and Replace feature to replace all Asterisks in the data:
Here are the steps:
- Click on Find & Select ->Replace from the Home tab (under the ‘Editing’ group). Alternatively you can press CTRL+H on the keyboard
- This will open the Find and Replace dialog box
- Enter “~*” in the ‘Find what’ field
- In the ‘Replace with’ field, enter what you want to replace the asterisk character with. If you simply want to delete it, you can leave this field blank
- Click on the ‘Replace All’ button
This will replace (or remove) all instances of the ‘*’ character from your worksheet.
Note: If you only want to replace a certain instance of the asterisk symbol, click on the ‘Replace’ button instead.
Once you’re done, you can close the Find and Replace dialog box by clicking on the Close button.
Using the SUBSTITUTE Function to Replace Asterisks in Excel
Another easy way to replace asterisks in Excel is by using the SUBSTITUTE function. This function can be used to replace or remove a specific character from a string. The general syntax for the function is:
=SUBSTITUTE (original_string, old_character, new_character, instance_number)
- original_string is the text or reference to the cell that you want to work on.
- old_character is the character that you want to replace.
- new_character is the character that you want to replace old_character with.
- instance_number is optional. It is the instance of the old_character that you want to replace.
It is also possible to customize the above formula to the make it suitable to remove a specific character from a string, as follows:
=SUBSTITUTE (original_string, old_character, “”)
This formula will replace the old_character with a blank (“”), which means the character will basically get deleted.
Let us assume you have the same set of string values with the asterisk (*) symbol in random places, and you want to replace all instances of them with a comma (,)
For this, you can use the SUBSTITUTE function with the following steps:
- Select the first cell of the column where you want the results to appear. In our example, it will be cell B2.
- Type the formula: =SUBSTITUTE(A2,”*”,”,”)
- Press the return key.
- This will give you the text obtained after removing all instances of the ‘*’ symbol in cell A2.
- Double click the fill handle of cell B2. or drag it down to copy the formula to the rest of the cells in the column.
Your dataset should now look like this:
Replacing only a Particular Instance of the Asterisk in Excel
Let us now consider the case where you want to remove a particular instance if the asterisk symbol. In this case, you can make use of the last optional parameter of the SUBSTITUTE function, because this lets you specify which instance of the symbol you want to remove.
This means, if you want to remove or replace the first instance of the character, your function syntax would be:
=SUBSTITUTE (original_string, old_character, “,”,1)
Similarly, if you want to replace or remove the second instance of the character, your function syntax would be:
=SUBSTITUTE (original_string, old_character, “,”,2)
In the following screenshot, let us say you want to replace only the first instance of the asterisk symbol in cell A2 with a comma. Your formula would then be:
=SUBSTITUTE (A2, “*”, “,”,1)
Here’s the result you would get:
Note that when using the SUBSTITUTE function, you don’t need to use an escape character before the asterisk symbol.
In this tutorial, we showed you two ways to replace or remove the asterisk symbol from your strings in Excel.
I hope you found it helpful and easy to follow.
Other articles you may also like: