Find & Replace With Wildcards in Excel & Google Sheets

This tutorial demonstrates how to find and replace text and values using wildcards in Excel and Google Sheets.

 

A wildcard is a character in Excel that can be used in place of another character. Excel uses the wildcards * (asterisk) and ? (question mark). An asterisk can represent any number of characters in a cell while a question mark represents just one character in a cell. The asterisk is the most commonly used wildcard.

Find and Replace Text

Find and Replace With Asterisk

To find and replace the entire contents of a cell, follow these steps:

1. Select the cells that contain the data to replace.

2. In the Ribbon, go to Home > Editing > Find & Select > Replace.

3. Now, type in the word you wish to find with the wildcard and the word you wish to replace it with. In the example below, in the Find what box, type “West*” and in the Replace with box type “Western“. This means that you are searching for any word that begins with West and has any number of characters after that are then replacing the found words with the word Western.
   To match the entire cells’ contents, tick Match entire cell contents.

This, therefore, finds words like West, Western, and Westerly and replaces them all with Western. Since it’s set to match entire cell contents, Find and Replace doesn’t find or change the phrases North West or South West.

4. Click Replace All to find all instances of the word.

Find and Replace Text String Within a Cell

If you remove the check mark from Match entire cell contents, then South West and North West are replaced with South Western and North Western. It doesn’t matter that each of those cells includes other text.

Find and Replace Cell Based on String

To replace all cells that contain the word in the Find what box, use a wildcard at the beginning and at the end of the word.

If you click Replace All this time, any cells containing the word West are replaced. North and South are, effectively, deleted.

Find and Replace Text With Question Mark

A question mark represents just one character in a word.

For example, if you are looking for all the words that start with the letters we, and end with the letter t, then you can type “we?t” into the Find what box.

For the example above, when you click Replace All, all words matching the pattern we?t are replaced with West.

As with the asterisk, if Match entire cell contents is checked, the values north west and went south are not changed.

However, if you remove that check mark, those cells are both changed.

Match Case

If you check the Match case check box, the search is case sensitive.

Note that, when you click Replace All with Match case checked, two of the words (Welt, Wert) won’t be replaced, since they begin with capital letters.

Find and Replace Values

1. Select the cells that contain the data you want to search.

2. In the Ribbon, go to Home > Editing > Find & Select > Replace.
3. In the Find what box, type in the value you wish to find, substituting an asterisk for an unknown or variable ending. So for example, if you type in “20*“, Excel finds all cells whose values start with 20, regardless of how they end.
   In the Replace with box, type in “2022“.
   Make sure Match entire cells contents is checked, and then click Replace All.

All the values starting with 20 are replaced with 2022.

Question Mark Wildcard for Values

You can also use a question mark to replace values.

1. Highlight the range of cells that contains the values and then in the Find and Replace box, use a question mark to replace the value in the cell.
   The example below searches for the third number in the value (beginning with 20) and replaces it with 2.
2. Make sure Match entire cell contents is not checked, so as to ignore the last digit in the cell value.

3. Click Replace All.

Find and Replace With Tilde (~)

If you want to search for an asterisk or a question mark in your workbook, Find and Replace won’t work properly without a tilde.

The example below uses the tilde to find any asterisk in the cells.

1. Select the cells that contain the asterisk to be replaced, and then in the Ribbon, go to Home > Editing > Find & Select > Replace.
2. In the Find what box, type in the value that you wish to find but add a tilde in front of the asterisk . So for example, if you type in “20~*” it finds all the cells whose values start with 20 and end in an asterisk.
   In the Replace with box, type in “2022“.
   Make sure Match entire cells contents is checked and then click Replace All.

Note that the only cells replaced are those that contain 20*. The ~* part of search term means: find an asterisk. (Without the tilde, * means: find any characters.)

See also: VBA Wildcards and VLOOKUP Contains (Partial Match)

Find and Replace With Wildcards in Google Sheets

Wildcards in Google Sheets work slightly differently. Although the wildcards * and ? do still work, they may end up giving you unexpected results!

An asterisk only represents one character, not multiple characters like it does in Excel.

1. Select the range of cells you wish to work with, and then, in the Menu, go to Edit > Find and Replace.

2. In the Find and Replace dialog box, type in what you want to search for, e.g., “20*2” in the Find box, and then type what you want to replace it with in the Replace with box, e.g., 2022.
   Then, make sure that Search using regular expressions is checked. Without this being checked, you cannot use a wildcard. This automatically checks Match case. Make sure that Match entire cell contents is also checked to make sure you get the correct results.
   Finally, click Replace All.

An asterisk in the Find box after a character in Google Sheets acts like a question mark does in Excel. So in the example above, Find and replace searches for four-digit values starting with 20 and ending with 2 and replaces those values with 2022. It does not replace 2000 or 2001, as they do not end with a 2.

To replace a set number of characters, use a period.

In the Find box, type in “20..” and then in the Replace box, type in “2022“. Once again, make sure Search using regular expressions is checked. Click Replace all.

This time, all four-digit values starting with 20 are replaced with 2022.