How to Find and Replace Using Wildcards in Excel

If we need to change cell values in bulk, we use the Find and Replace dialog box. However, sometimes we may need to change cell values with partial match. We can easily use Wildcards to Find and Replace those cell values. In this article, we will learn how to use wildcards to Find and Replace cell values in Excel.

Contents hide

📁 Download Excel File

What Are Wildcards in Excel?

Learn to Find and Replace in Excel with These 3 Wildcards

1. Using Asterisk Wildcard (*)

2. Utilizing Question Mark Wildcard (?)

3. Executing Tilde Wildcard (~)

📝 Takeaways from This Article

Conclusion

📁 Download Excel File

Download the following Excel workbook to realize the topic more clearly.

Find and Replace Using Wildcards.xlsx

What Are Wildcards in Excel?

A wildcard is a particular type of character in Excel that can replace any other character. Especially, if you do not know a definite character, you can substitute a wildcard. Excel has 3 wildcards such as Asterisk (*), Question mark (?), and Tilde (~) symbols.

Learn to Find and Replace in Excel with These 3 Wildcards

We will discuss how to Find and Replace using the available 3 wildcard characters with the help of the following dataset.

Approach

1. Using Asterisk Wildcard (*)

We can use the Asterisk wildcard to Find and Replace any amount of characters. Let’s see how we can use the Asterisk symbol to Find and Replace in Excel.

s*t can Find and Replace the words start, started, student.
*st can find the words trust, frost, lost.
st* can be used to match star, steward, steps.

⬇️⬇️ STEPS ⬇️⬇️

In the beginning, let me show you how to open the Find and Replace dialog box.
Under the Home tab, click Find & Select in the Editing group.

From the menu, you can either select Find or Replace.

Momentarily, the Find and Replace dialog box will appear on the screen. Or, you can just press Ctrl+F concurrently to open the dialog box.

Next, we will use the Asterisk symbol to Find and Replace Years starting with 20.
Now, from the Find tab, type 20* in the Find what box and click Find All.

Momentarily, the dialog box will show the Years starting with 20 including the workbook, sheet, and even cell numbers and values.

Afterward, if you want to replace the Years with something click the Replace tab.
Type in the value you want to display in the Replace with box and click Replace All.

A tiny dialog box will show how many changes were applied. Click OK and Close the Find and Replace dialog box.

You can see the results in the following screenshot.

Approach

2. Utilizing Question Mark Wildcard (?)

Unlike the Asterisk symbol, a Question mark wildcard can only find one character. However, we can use multiple question marks to find multiple characters. For instance:

so?t will locate sort, soft.
wi?? can find wire, wild, wipe.

⬇️⬇️ STEPS ⬇️⬇️

Initially, press Ctrl+F simultaneously to open the Find and Replace dialog box.
We want to Find and Replace Canada-Mexico-USA from our dataset. Just like we discussed, we need to put a Question mark for every character.
Under the Find tab, type in ??????-??????-??? And click Find All.

Excel will find the one cell that accommodates Canada-Mexico-USA.

Later, to replace the value with North America, we navigate to the Replace tab, enter North America in the Replace with box and click Replace All.

We have replaced Canada-Mexico-USA with North America. Great!

Note: If you write ??????- in the Find what box, Excel will find the cell containing Canada-Mexico-USA. However, if you want to replace the value with North America, it will appear twice in that cell like North AmericaNorth AmericaUsa. Since Canada and Mexico both have 6 letters, Excel finds both of them in one cell and changes both of them with North America.

Approach

3. Executing Tilde Wildcard (~)

Tilde wildcard can find the wildcards asterisk (*), question mark (?), and even tilde (~) in the whole workbook. For example:

what~? finds what?
*~** can find an asterisk like stop*, st*op.
~~ will find cells containing a tilde (~).
*~? finds any cell value that ends with a question mark i.e. how?, hello?.

⬇️⬇️ STEPS ⬇️⬇️

First of all, press Ctrl+F together to summon the Find and Replace dialog box.
Next, we want to find the cells containing the asterisk symbol.
Write *~** in the Find what box and click Find All. Here, the third asterisk is the one we are looking for. The rest are used as wildcards to find the asterisks.

Excel will find the cells containing the asterisk symbol in a jiffy.

At this time, type TBD in the Replace with box under the Replace tab and click Replace All to replace the cell value accommodating the asterisk symbol.

Just like in the picture below, the cell value has changed to TBD.

Similarly, to find the question mark in the dataset, type ~? in the Find what box.

📝 Takeaways from This Article

📌 Firstly, we learned how to use Asterisk Wildcard to Find and Replace multiple characters.

📌 Secondly, we discussed the role of the Question mark wildcard to find and replace characters.

📌 Finally, we illustrated the use of the Tilde wildcard to find and replace the wildcards themselves.

Conclusion

That concludes the discussion for today. These are some convenient methods to Find and Replace using wildcards in Excel. If you have any queries or recommendations, kindly notify us in the comments section. Downloading the practice sheet will help you comprehend the concepts better. Visit our website ExcelDen to find out about diverse kinds of Excel methods. We appreciate you taking the time to read this article.

(Visited 105 times, 1 visits today)