How to Find and Replace Asterisk (*) Character in Excel

While working with Microsoft Excel, we occasionally have to change the data and to do so, we must replace terms with other terms or letters and numbers or characters. In this article, we will demonstrate various ways to find and replace an asterisk (*) character in excel.


Download Practice Workbook

You can download the workbook and practice with them.


4 Ways to Find and Replace Asterisk (*) Character in Excel

With Excel, we can easily find and replace characters as per the requirements. To find and replace the asterisk (*) character in excel, we are going to use the following dataset. The dataset contains some employee details with ‘*’ in column B, and we want to replace the ‘*’ character with ‘,’ in column C. Now, let’s look at the different methods to do this.

Excel Find and Replace * Character


1. Use Ribbon to Find and Replace Asterisk (*) Character in Excel

We can use the Find and Replace features from the ribbon to find and replace the ‘*’ character. The Find feature allows searching for words or formats in a document or files and the Replace feature allows to replace all instances of a term or format. Find & Replace is a command on Excel that allows us to swiftly and effectively search for certain words or concepts and replace those as per our requirements.

So, let’s look at the steps down to find and replace * character using the find and replace command in excel.

STEPS:

  • Firstly, go to the Home tab on the ribbon.
  • Secondly, click on the Find & Select drop-down menu from the Editing group.
  • Third, click Find.

  • This will open the Find and Replace dialog box.
  • Now, as we want to find the asterisk, so we type * on the Find what type box.

  • Next, go to the Replace menu, replace ‘*’ with ‘,’ and then, click on Replace All.

Excel Find and Replace * Character

  • This will replace all the characters with ‘,’.

Excel Find and Replace * Character

  • To replace the asterisk (*) sign only, we have to put a ‘~’ sign before the asterisk (*). Click on Replace All.

  • And, finally, by doing this, all the text will be visible, and the ‘*’ character will be replaced with ‘~’.

Read More: How to Find and Replace Using Formula in Excel (4 Examples)


2. Excel FIND and REPLACE Functions to Find and Replace ‘*’ Character

We can find and replace the ‘*’ character by combining excel FIND and REPLACE functions. In Excel, the FIND function is used to find the location of a certain character or substring inside a text string. When you need to find a character in Excel, this function is likely to become the first choice to go with, as well as for excellent purpose.

And, the REPLACE function replaces characters in a text string defined by location with characters from another text string. This function substitutes characters in a text string in order of appearance. This function is beneficial when the position of the data to be changed is known or easily ascertained.

Suppose, we want to shorten the name of column B in column C. We will only take the first letter of the name and replace the ‘*’ character with the dot (.). To use the combination of the FIND and REPLACE function, we need to follow the steps down.

STEPS:

  • First, select the cell where you want to put the formula. So, we select cell C5.
  • Second, put the formula in the selected cell.
=REPLACE(B5,1,FIND("*",B5),LEFT(B5)&".")
  • Then, press Enter.

In this case, the REPLACE function uses the cell reference B5, counts the letters until it finds a ‘*’ in it using the FIND function, and then uses the LEFT function to replace the first name with its starting letter and a dot (.).

  • Now, drag the Fill Handle down to copy the formula over the column.

Excel FIND and REPLACE Functions to Find and Replace ‘* Character

  • And, that’s it! You will be able to see the result in the resulted cell.

Read More: How to Find and Replace from List with Macro in Excel (5 Examples)


Similar Readings:


3. Apply SUBSTITUTE Function to Find * Character and Replace

We can apply the SUBSTITUTE function to find and replace the ‘*’ character. The Excel SUBSTITUTE function replaces one or more occurrences of a defined string. To use this function to find and replace the ‘*’ character, we have to look at the procedure.

STEPS:

  • In the beginning, by the same token as before, we need to choose a cell to put the formula there. So, we choose cell B5.
  • Then, enter the formula into that selected cell.
=SUBSTITUTE(B5,"*",",")
  • After that, press Enter.

  • Next, to copy the formula over the column, drag the Fill Handle down.

Apply SUBSTITUTE Function to Find * Character and Replace

  • And, finally, all the ‘*’ characters will be replaced with ‘,’.

4 Ways to Find and Replace Asterisk (*) Character in Excel

Read More: How to Substitute Multiple Characters in Excel (6 Ways)


4. Find and Replace Asterisk (*) Character with VBA in Excel

We can also find and replace the asterisk (*) character by using Excel VBA. Suppose we have a list of names with ‘*’. Now we want to replace it with space and we will see the result in Excel VBA MsgBox. For this, we have to follow the steps below.

STEPS:

  • In the first place, go to the Developer tab from the ribbon.
  • Next, click on Visual Basic or press Alt + 11 to open the Visual Basic Editor.

  • Instead of doing this, you can open the Visual Basic Editor by right-clicking on the sheet and selecting View Code.

  • This will appear the Visual Basic Editor where you can write your code.
  • Now, write down the VBA code there.

VBA Code:

Sub Find_and_Replace()
string1 = "Jhon*Barker, Alex*Jane, Robert*Chao, Sally*Brooke"
string1 = Replace(string1, "*", " ")
MsgBox string1
End Sub
  • After that, run the code by pressing the F5 key or clicking on the Rub Sub button.

  • This will show the result in the MsgBox.

Read More: Excel VBA to Find and Replace Text in a Column (2 Examples)


Conclusion

The above methods will assist you to find and replace the asterisk ‘*’ character in Excel. Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section. Or you can have a glance at our other articles in the ExcelDemy.com blog!


Related Articles

Sabrina Ayon

Sabrina Ayon

Hi there! This is Sabrina Ayon. I'm really excited to welcome you to my profile. Currently, I'm working in SOFTEKO as a Excel and VBA Content Developer. I'm a student of United International University and my program is Computer Science and Engineering. I love working with computers and solving problems. I’ve always been interested in research and development. So, here I will be posting articles related to Microsoft Excel. Hoped this may help you. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo