Shakil Ahmed
Today we are going to show you how to find and replace using wildcards. 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. Excel has 3 wildcard characters, namely, Asterisk, Question Mark, and Tilde. These characters are used to search or find results that are approximate or less accurate.
Download Practice Workbook
You are welcome to download the practice workbook from the link below.
What Is Find and Replace with Wildcards in Excel?
In this article, we will use the Wildcards find and replace values. Firstly, we will use the Asterisk character to find and replace values. Finally, we will use the Question Mark sign to do the business. 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. 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.
1. Using Asterisk as Wildcards
In this method, we will use the Asterisk character to find and replace in Excel. The Asterisk is used to match zero or any number of characters. In this example, we will use the sign in front and back of a string and try to find out all the values that matches that string.
Steps:
- Firstly, go to the Editing group in the Home tab.
- Secondly, select the Find & Select option.
- Finally, from the drop-down options, choose Replace.
- Consequently, a prompt will be on the screen.
- In the prompt, type “*res*” in the Find what box.
- Then, press Find All.
- As a result, Excel will find out all the partial matches.
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.
- Now, select the Replace with box and type “Prestige”.
- Then, choose Replace All.
- As a result, all the found value will be replaced by “Prestige“.
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.
Read More: How to Find * Character Not as Wildcard in Excel (2 Methods)
2. Applying Question Mark as Wildcards
In this instance, we will apply the Question Mark sign to find and replace values in Excel. Unlike the Asterisk, it only denotes the number of characters equal to the question mark.
Steps:
- To begin with, go to the Home tab and select the Editing group.
- Then, select the Find & Select option.
- Finally, from the available options, select Replace.
- As a result, a prompt will appear on the screen.
- In the prompt, enter “Ocean?s” in the Find what box.
- Next, click on Find All.
- Consequently, Excel will find out all the partial matches.
Excel will show Ocean’s Eleven, Ocean-s Twelve (for demonstrating various cases we have rewritten Ocean’s Twelve as such) for this search value.
- Thereafter, choose the Replace with box and enter “Oceans”.
- Then, press Replace All.
- As a result, Ocean’s and Ocean-s will be replaced by Oceans.
We could also click on Replace only. It would replace the then selected cell of matching value and switch to the next cell where it finds the wildcard resemblance. Here, we have found and replaced values using the question mark as a wildcard.
Similar Readings
- Replace Special Characters in Excel (6 Ways)
- VLOOKUP with Wildcard in Excel (3 Methods)
- Use the Substitute Function in Excel VBA (3 Examples)
- Find And Replace Multiple Values in Excel (6 Quick Methods)
- INDEX MATCH Multiple Criteria with Wildcard in Excel (A Complete Guide)
3. Using Asterisk and Question Mark Together
In this method, we will use the Asterisk and Question Mark signs together to find and replace values. This method gives flexibility to the users while searching. By using these two signs together, we can do both single and multiple searching at a time.
Steps:
- To start with, press Ctrl+F.
- As a result, the Find prompt will be on the screen.
- In the prompt, write “Ocean?s*” in the Find what box.
- After that, choose on Find All.
- As a result, Excel will find out all the partial matches.
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.
- Now, go to the Replace tab.
- In the Replace with box, write “Oceans”.
- Then, press Replace All.
- Consequently, all the partially matched values will be replaced by Oceans.
This takes count of all three cells of Ocean’s series and replaced the entire cell with Oceans unlike the previous method where the Question Mark only replaced the Ocean’s part.
Read More: Search for Question Mark in Excel (4 Suitable Methods)
Conclusion
In this article, we have discussed how to find and replace values using wildcards in Excel. 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.
