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.
How to Find and Replace Asterisk (*) Character in Excel: 4 Ways
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.
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.
- 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 ‘~’.
Read More: How to Find and Replace Values Using Wildcards in Excel
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 purposes.
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 functions, 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.
- And, that’s it! You will be able to see the result in the resulted cell.
Read More: How to Find and Replace Using Formula in Excel
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.
- 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.
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.
Download Practice Workbook
You can download the workbook and practice with them.
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.
Related Articles
- Find and Replace Tab Character in Excel
- [Fixed!] Excel Find and Replace Not Working
- How to Find and Replace in Excel Column
- Find And Replace Multiple Values in Excel
- How to Replace Special Characters in Excel
- How to Substitute Multiple Characters in Excel
<< Go Back to Find and Replace | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
