This tutorial will demonstrate how to find and replace question marks and asterisks in Excel.
Replace an Asterisk
Occasionally, data in Excel will need cleaning up. To replace an asterisk (*) in the data with a comma, you can use the Replace feature in Excel.
In the Ribbon, select Home > Find & Select > Replace.
Normally, when you use the Replace feature in Excel, you just type in the character you want to find, then the character you wish to replace it with.
However, when looking for certain special characters such as the asterisk, you cannot just insert an asterisk into the Find what text box. This is due to the fact that the asterisk is a wildcard and the Replace feature would then replace all text with a comma.
Instead, when entering an asterisk into the Find what text box, precede it with a tilde (~). The tilde can be found in the top left hand side of the keyboard above the tab key.
Now when you click Replace All or Replace, only the actual asterisk will be replaced with the comma from the Replace with text box.
Replace a Question Mark
Similarly, replacing a question mark must be done the same way; the question mark is also a wildcard.
Once again, put a tilde in front of the question mark before you click Replace or Replace All.
Clicking Replace All will then only replace the question marks and not the rest of the text.
The SUBSTITUTE Function
A different way to replace a text string with another text string is to use the SUBSTITUTE Function.
Click in the cell where you want the adjusted text string to go.
Type the formula. For example:
=SUBSITUTE(B2, "*", ",")
Note that the asterisk and the comma replacing it must both be contained within quotation marks.
You can also use this function to replace the question mark with a comma, or any other text string with another text string.
Replace an Asterisk or Question Mark in Google Sheets
The SUBSTITUTE Function works exactly the same in Google Sheets as it does in Excel.
The Find and replace feature in Google Sheets does not treat asterisks or question marks as wildcards, so there’s no need to use the tilde when replacing those characters.