Find & Replace Question Marks and Asterisks in Excel & Google Sheets

This tutorial will demonstrate how to find and replace question marks and asterisks in Excel.

 

replaceasterix intro

 

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.

 

replace find select

 

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.

 

replace find replace

 

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.

 

replace replaced asterix null

 

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.

 

replace find replace tilde

 

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 replaced comma

 

Replace a Question Mark

Similarly, replacing a question mark must be done the same way; the question mark is also a wildcard.

 

replace replaced questionmark

 

Once again, put a tilde in front of the question mark before you click Replace or Replace All.

 

replace tilde questionmark

 

Clicking Replace All will then only replace the question marks and not the rest of the text.

 

replace replaced comma

 

The SUBSTITUTE Function

A different way to replace a text string with another text string is to use the SUBSTITUTE Function.

 

replace 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.