See all How-To Articles

Find & Replace With Wildcards in Excel & Google Sheets

This article will demonstrate how to find & replace using wildcards in Excel & Google Sheets.

Wildcards intro

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

Find and Replace Text in a Cell

Find and Replace Text in a Cell with an Asterix

Select the cells that contain the data that you need to replace.

Wildcards select cells

 

In the Ribbon, select Home > Editing > Find & Select > Replace.

Wildcards ribbon

You can now put in the word you wish to find with the wild card, and then the word you wish to replace it with.   In the example below, (1) in the Find what box, type “West*” and in the Replace with box type “Western“,  This means that we are searching for any word that begins with West and has any number of characters after that.   We are then replacing the found words with the word Western.   We wish to match the entire cells contents to make sure that (2) Match entire cell contents is checked.

Wildcards replace

This will therefore find words like West, Western and Westerly  and replace them all with Western.  Due to the fact we asked to “Match entire cell contents”,  it will not find the phrases “North West” or “South West” and therefore will not change them.

Click Replace All to find all instances of the word.

Wildcards select replaced

If we remove the check mark from the “Match entire cell contents” box, then it will replace “South West” and “North West” with “South Western” and “North Western

Wildcards select match contents

To replace all the cells that contain the word in the “Find what” box, we can use a wildcard at the begining and at the end of the word.

Wildcards double wildcard

If we click Replace All this time, it will replace any cells containing the word “West”.

Wildcards select replaced all

Find and Replace Text in a Cell with an Asterix with a Question Mark

A question mark replaces just one character in a word.

wildcards select question find

As with the asterix, if “Match entire cell contents” is checked, the values “north west” and “went south” will not be amended.

wildcards select question replaced

However if we remove the check mark, they will both be amended.

Wildcards select-question match contents

Matching Case

If we were to check the Match case check box, using either the Asterix or the Question mark, then only words or phrases would be found that match the case that we type into the Find what box.

Wildcards-select match case

Now when we click replace all, 2 of the words will not be found as they begin with capital letters.

Wildcards-select match case replaced

 

Find and Replace Values in a Cell

Select the cells that contain the data that you need to replace.

Wildcards select values

In the Ribbon, select Home > Editing > Find & Select > Replace.

In the Find what box, (1) type in the value plus an asterix that you wish to find – so for example if you type in 20* it will find all the cells whose values start with 20 regardless of what they end in.  In the Replace with box, type in 2022.   Make sure (2) Match entire cells contents is checked and then click Replace All.

Wildcards select replace values

All the values starting with 20 will be replaced with 2022.

Wildcards-select replace values replaced

We can also use a question mark to replace values.

Highlight the range of cells that contain the values and then in the Find and Replace box, type in a question mark to replace the value in the cell.  In the example below, we are looking for the third number in the value, and replacing it will a 2.   Make sure “Match entier cell contents” is NOT checked as we want to ignore the last number in the cell value.

Wildcards select-replace values questionmark

Click Replace All to replace the values.

 

Wildcards select-replaced values questionmark

Find & Replace with the Tilde (~)

The Tilde is used to convert the asterix and question mark into normal characters in Excel find and replace.

In the example below, we  can use the tilde to find any asterix in the cells.

Select the cells that contain the asterix to be replaced, and then in the Ribbon, select Home > Editing > Find & Select > Replace.

In the Find what box, (1) type in the value plus a tilde in front of the asterix that you wish to find – so for example if you type in 20~* it will find all the cells whose values start with 20 and end in an asterix.  In the Replace with box, type in 2022.   Make sure (2) Match entire cells contents is checked and then click Replace All.

Wildcards select find asterix

 

You will notice that not all the cells are affected – only the cells whose values start with 20 and then end in an asterix are changed.  The other cells remain at their original value as the tilde actually looks for an replaces the asterix, rather than the asterix being used as the wildcard.

Wildcards select replaced tilde

 

Find & Replace With Wildcards in Google Sheets

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

An asterix is only able to be used to replace 1 character, not multiple characters.

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

Wildcards gs menu

In the Find and Replace dialog box, (1) type in what you want to search for eg: 20*2 in the Find box, and then type what you want to replace it with in the Replace with Box eg: 2022.

Then (2), make sure that “Search using regular expressions” is checked.  Without this being checked, you cannot use a wildcard. This will automatically then check “Match case”.  Make sure that “Match entire cell contents” is also checked to make sure you get the correct results.  Finally, (3) click Replace All.

Wildcards gs asterix find

The asterix in the Find box after a character means that Google sheets will search for that actual character – so in the example above, it will search for any values starting with 20 or 200 and ending with a 2, and will replace those values with 2022.  It will not replace 2000 or 2001 as they do not end with a 2.

Wildcards gs replaceasterix

To replace a set number of characters, we use the 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.

Wildcards gs replace period

This time, all he values starting with 20 and containing 4 number will be replaced with 2022.

Wildcards gs replaced period