Shakil Ahmed
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.
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.
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).
In the Find What field write the data (value) you are looking for. Then click Find Next or Find All.
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).
Here, we are going to replace 2006 with 006. So, let’s write 006 down to the Replace with field. And click Replace.
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.
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.
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.
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.
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.
One thing we can do, adjust the finding value from *res* to *Pres*, this has wildcards and will find and replace only The Prestige.
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.
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.
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.
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.
When you want to change a bunch of values, you can use Replace All, it will modify all the values at once.
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.
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.
