How to Find And Replace Values Using Wildcards in Excel

While dealing with data in Excel you may need to eradicate errors or modify your data regularly. Removing errors or regular modification, on both occasions, you need to find data and replace that. This “find and replace” process can be done by allowing wildcards.

Today we are going to show you how to find and replace using wildcards. First things first, let’s get to know about the dataset which is the base of our examples.

Dataset - Excel Find And Replace Wildcards

Here, we have a table that contains information about several movies. Using this dataset, we will use wildcards for finding and replacing data.

Note that this is a simple dataset to keep things straightforward. In a practical scenario, you may encounter a much larger and complex dataset.

Practice Workbook

You are welcome to download the practice workbook from the link below.

Find And Replace Using Wildcards

The Find And Replace Feature

Excel provides a Find & Replace feature to search and replace any data. To utilize the feature you need to explore the Find & Select option within the Editing section from the Home tab.

Find & Selecting option - Excel Find And Replace Wildcards

From there you will find the Find option. You will find a Find and Replace dialog box by clicking the Find option (shortcut key – CTRL + F).

Find & Replace Dialog box to find value

In the Find What field write the data (value) you are looking for. Then click Find Next or Find All.

Click Find All to find wildcard value

Here we have searched 2006 and clicked Find All. Now, you can see the cell, has the searched value, is selected.

Now, let’s say we want to change this value. Then we need to switch to the Replace window of this dialog box (you can directly open that by pressing CTRL + H).

Replace window from Find & Replace box

Here, we are going to replace 2006 with 006. So, let’s write 006 down to the Replace with field. And click Replace.

Click Replace to replace value

You can see it replaced the originally stored 2006 to 006. This is how the Find and Replace works.

Find and Replace With Wildcards

In the earlier section, we have tried to make you familiar with the Find and Replace feature (if you were not earlier). There we found and replaced the value where the value is not a substring within a long value. Exact match, you can say.

You can search for a substring and replace that using wildcards with Find and Replace. Hearing wildcards, probably partial matches appear in your mind.

Knowing partial matches (e.g. partial text match, partial string match, IF partial match) will let you understand the procedure much quicker.

1. Asterisk as Wildcards

For wildcards, we can use the asterisk sign (*). It denotes that any character (including no character) can take place any number of times. Let’s explore with example.

Asterisk as wildcard - Excel Find And Replace Wildcards

Here we have searched *res* and found that Excel points out to the cell that contains The Prestige.

You may wonder how res can be matched with The Prestige! We have used an asterisk sign (*) at either side of res. That denotes that any number of characters can surround res.

The word Prestige has res within it that’s why it founded it. And so did for the Reservoir Dogs.

Find the wildcard value - Excel Find And Replace Wildcards

As we found the value using a wildcard, now if we want to replace the word, switch to Replace window.

Let’s say, we aim to change The Prestige to Prestige only.

Replace a value using asterisk as wildcard

Write Prestige in the Replace with field. You can see there are two options Replace All and Replace. If we use Replace All, then it will replace both the cells that contain res.

Replace All option to replace values - Excel Find And Replace Wildcards

We can use the Replace option, though this will replace the intended value, then point to the value that we don’t want to replace.

Replace option click to replace wildcard values

One thing we can do, adjust the finding value from *res* to *Pres*, this has wildcards and will find and replace only The Prestige.

Change in wildcard search value
Here we find and replace the value The Prestige using wildcards. 

2. Question Mark as Wildcards

Another way of using wildcards is the question mark (?). Unlike asterisk, it only denotes the number of characters equal to the question mark.

Let’s explore with examples.

Question mark as wildcard - Excel Find And Replace Wildcards

Find the value first (CTRL + F). Here we have searched Ocean?s. That means there can be only character between Ocean and s.

Clicking Find Next we found Ocean’s Eleven for this search value. Let’s traverse more.

FIND Next to find values in traversing order

This time we find Ocean-s Twelve (for demonstrating various cases we have rewritten Ocean’s Twelve as such).

Let’s replace the Ocean’s / Ocean-s with Oceans.

Replace Question mark wildcard values

Write Oceans in the Replace with field. And click Replace.

It will replace the then selected cell of matching value and switch to the next cell where it finds the wildcard resemblance.

Replace value and select next value

When you want to change a bunch of values, you can use Replace All, it will modify all the values at once.   

Replace All to replace Question mark wildcards values

Here, we have found and replaced values using the question mark as a wildcard.

We can use the asterisk and the question mark together as wildcards.

Asterisk & Question mark together as wildcards

Here we have searched and replaced Ocean?s* that denotes that there should be a character between Ocean and s, and there can be any character after the s.

This takes count of all three cells of Ocean’s series and replaced the entire cell with Ocean 

Conclusion

That’s all for today. We have listed approaches to find and replace wildcards in Excel. Hope you will find this helpful. Feel free to comment if anything seems difficult to understand. Let us know any other methods that we might have missed here.

Tags:

Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo