How to Replace Asterisks in Excel

by

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:

Example Data with asterisks

Here are the steps:

  1. Click on Find & Select ->Replace from the Home tab (under the ‘Editing’ group). Alternatively you can press CTRL+H on the keyboard
Click on the replace option
  1. This will open the Find and Replace dialog box
Find and Replace dialog box
  1. Enter “~*” in the ‘Find what’ field
  2. 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
Enter asterisk in the ‘Find what’ field
  1. Click on the ‘Replace All’ button
Click on Replace all button

This will replace (or remove) all instances of the ‘*’ character from your worksheet.

Data where asterisks have been removed

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.

Close the find and replace dialog box
Also read: SEARCH vs FIND Function in Excel

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)

Here,  

  • 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 (,)

data with asterisk

For this, you can use the SUBSTITUTE function with the following steps:

  1. Select the first cell of the column where you want the results to appear. In our example, it will be cell B2.
  2. Type the formula: =SUBSTITUTE(A2,”*”,”,”)
  3. Press the return key.
  4. This will give you the text obtained after removing all instances of the ‘*’ symbol in cell A2.
  5. 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:

Substitute function to replace asterisks

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:

substitute function to remove the first instance of asterisk

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:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment