How to Find and Replace the Asterisk (*) Character in Excel – 4 Methods

The dataset below showcases employees’ details with an ‘*’ in column B.

To replace the ‘*’ with ‘,’ in column C:

Method 1 – Use the Find and Replace Feature to replace the Asterisk (*) 

STEPS:

• Go to the Home tab.
• Click Find & Select in Editing.
• Click Find.

• In the Find and Replace dialog box, enter “*” in Find what:.

• In Replace with:, enter ‘,’ and click Replace All.

• This is the output.

• To replace the asterisk (*) sign only, enter ‘~’ before the asterisk (*).
• Click Replace All.

• The text will be visible, and the ‘*’ is replaced with ‘~’.

Method 2 – Using the Excel FIND and REPLACE Functions to Find and Replace the ‘*’ 

The FIND function finds the location of a character or substring inside a text string. The REPLACE function replaces characters in a text string in their order of appearance.

To shorten the names in column C, taking the first letter of the name and replacing ‘*’ with (.):

STEPS:

• Select C5 and enter the formula:

• Press Enter.

The REPLACE function uses the cell reference B5, counts the letters until it finds ‘*’, using the FIND function. The LEFT function replaces the first name with its starting letter and a dot (.).

• Drag down the Fill Handle to see the result in the rest of the cells.

This is the output.

Read More: How to Find and Replace Using Formula in Excel

Method 3 – Applying the  SUBSTITUTE Function to Find and Replace * 

Use the SUBSTITUTE function. It replaces one or more occurrences in a defined string.

STEPS:

• Select C5 and enter the formula:

• Press Enter.

• Drag down the Fill Handle to see the result in the rest of the cells.

• This is the output.

Method 4 – Find and Replace the Asterisk (*) with VBA in Excel

To replace ‘*’ with a space and the result in a MsgBox:

STEPS:

• Go to the Developer tab.
• Click Visual Basic or press Alt + 11 to open the Visual Basic Editor.

• You can also open the Visual Basic Editor by right-clicking the sheet and selecting View Code.

• Enter the code in the Visual Basic Editor:

VBA Code:

Sub Find_and_Replace()
string1 = "Jhon*Barker, Alex*Jane, Robert*Chao, Sally*Brooke"
string1 = Replace(string1, "*", " ")
MsgBox string1
End Sub

• Run the code by pressing F5 or clicking Run Sub button.

• This is the output.

Download Practice Workbook

Download the workbook and practice.

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