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.
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.
- 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.
- This will replace all the characters with ‘,’.
- 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 ‘~’.
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.
- First, select the cell where you want to put the formula. So, we select cell C5.
- Second, put the formula in the selected cell.
- 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.
- And, that’s it! You will be able to see the result in the resulted cell.
- How to Find and Replace Values in Multiple Excel Files (3 Methods)
- How to Find and Replace in Excel Column (6 Ways)
- Find and Replace a Text in a Range with Excel VBA (Macro and UserForm)
- [Fixed!] Excel Find and Replace Not Working (6 Solutions)
- How to Find and Replace Multiple Words at Once in Excel (7 Methods)
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.
- 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.
- After that, press Enter.
- Next, to copy the formula over the column, drag the Fill Handle down.
- And, finally, all the ‘*’ characters will be replaced with ‘,’.
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.
- 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.
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.
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!
- Add Text and Formula in the Same Cell in Excel (4 Examples)
- Replace Text of a Cell Based on Condition in Excel (5 Easy Methods)
- How to Use the Substitute Function in Excel VBA (3 Examples)
- How to Find And Replace Values Using Wildcards in Excel
- Data clean-up techniques in Excel: Adding text to cells