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

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

 

Replace an Asterisk

Occasionally, data in Excel needs cleaning up. For example, you might start off with a dataset full of asterisks you don’t want. To replace an asterisk (*) in the data with a comma, use Find and Replace. But there’s a catch!

1. In the Ribbon, go to Home> Find & Select> Replace (or use the keyboard shortcut CTRL + H).

Typically, with Find and Replace, 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 asterisks, you cannot just type an asterisk into the Find what box. This is because the asterisk is a wildcard and Find and Replace would then replace all text with a comma.

2. Instead, when typing an asterisk into the Find what box, precede it with a tilde (~). (The tilde is on the top-left side of the keyboard above the TAB key).

3. Now when you click Replace All or Replace, only the actual asterisk is replaced with the comma from the Replace with box.

Tip: See more about wildcard characters and how they are used in formulas.

Replace a Question Mark

Replacing a question mark must be done similarly as the question mark is also a wildcard.

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

2. Then, clicking Replace All only replaces the question marks and not the rest of the text.

Warning: If you did not use a tilde before the question mark, you would end up with the following result!

The SUBSTITUTE Function

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

1. Click in the cell where you want the adjusted text string to go.
2. Type in the formula. For example:

=SUBSTITUTE(B2,"*",",")

Note that the asterisk and the comma replacing it must both be contained within double quotation marks.

You can use the SUBSTITUTE Function to replace question marks with any other text string, too.

Replace * or ? in Google Sheets

The SUBSTITUTE Function works the same in Google Sheets as it does in Excel.

Find and Replace in Google Sheets does not treat asterisks or question marks as wildcards, so there’s no need to use a tilde when replacing those characters.