Wildcards in Excel’s Find and Replace function are like smart search helpers. They help you find and change the text in a clever way.
This is a very handy tool when you’re looking for words or phrases that have different versions. It’s a shortcut to finding and fixing mistakes in your data, it’s especially invaluable when your data is big or messy.
You can use three types of wildcards in Excel Find and Replace:
- * (Asterisk) – Use to represent any sequence of characters
- ? (Question mark) – Use to represent any single character
- ~ (Tilde) – Use to indicate that you want to find an actual wildcard character
In this article, we’ll show you various ways to use wildcards effectively within Excel’s find and replace functionality.
So, whether you’re new to the concept or looking to refine your skills, follow along to learn valuable tips and tricks for working with wildcards in Excel.
Find and Replace Feature in Excel
Excel’s Find and Replace feature is a powerful tool that helps you to search for specific text, values, or characters within your worksheets and replace them with new information.
This feature is especially useful when you need to make multiple changes or updates within your data.
Accessing the Find and Replace Options
To access the Find and Replace options in Excel, follow these simple steps:
- Click on the Home tab in the Ribbon.
- In the Editing group, click on Find & Select.
- Choose either Find or Replace from the dropdown menu. Alternatively, you can use the keyboard shortcuts Ctrl + F for Find and Ctrl + H for Replace.
- Then, Excel will open the “Find and Replace” dialog box.
- Enter your search criteria in the Find what box of the Replace window.
- Enter the replace text in the Replace with box.
- Click the Replace All button or Replace button.
In addition to these basic steps, you can further customize your search by using various options within the Find and Replace dialog box.
For example, you can search by rows and columns, search within comments or values, search within worksheets or entire workbooks, and options such as “Match case” or “Match entire cell contents.”
By using wildcard characters such as asterisks (*) and question marks (?), you can create more flexible search criteria, allowing you to find and replace text or values more efficiently.
Understanding Wildcards in Excel
Wildcards are special characters that can be used to find and replace text in your workbooks more efficiently.
Wildcard characters can help you further refine your search by representing any number of characters or specific characters in your search criteria.
Wildcard Characters
There are three main wildcard characters in Excel:
- Asterisk (*)
- Question mark (?)
- Tilde (~)
Asterisk (*)
An asterisk represents any number of characters.
For example, if you type A*, Excel will find any text that starts with “A” and has any number of characters after it.
Question mark (?)
A question mark represents any single character.
For example, if you type A?? in the Find box, Excel will find any three-letter text that starts with “A”.
Tilde (~)
In Excel, the tilde (~) has a unique role as a special character. It is primarily used to force Excel to treat certain characters as regular text rather than their intended special function.
A tilde indicates that you want to find an actual wildcard character (such as *, ?, or ~). To do this, you need to place the tilde before the wildcard.
For example, when using wildcard characters like an asterisk (*) and a question mark (?), you can use a tilde in front of them to treat them as regular characters.
- ~*: This means Excel will treat the asterisk (*) as a regular character, not as a wildcard representing any number of characters.
- ~?: Similarly, this means Excel will treat the question mark (?) as a regular character, not as a wildcard representing a single character.
The tilde character helps you to handle special characters like asterisks and question marks as regular text in Excel’s find and replace functionality.
It ensures that you can accurately search for and work with strings containing these special characters without confusion.
In summary, using wildcards in Excel can help you work more efficiently when finding and replacing text in your workbook.
The three main wildcard characters (asterisk, question mark, and tilde) provide the flexibility you need to manage your data.
Using Wildcards in Find and Replace
Now that you understand how to find and replace in Excel and use wildcards.
Let’s use both skills to make your workbook more flexible.
Example
The below table shows the product stages of new products.
In the above table, the letter “S” of the word “Test” is written using different characters.
Now, you can correct this with Excel finds and replace wildcard characters.
Here’s how to replace text using wildcards:
- Select the cells where you want to make changes. In this case, it is cells B2 to B6.
- Press Ctrl + H to open the Replace dialog box or go to Home > Editing > Find & Select > Replace.
- In the “Find what” box, type the text pattern with wildcard characters.
In this case, you can type te?t. The question mark in this word represents any single character. - In the “Replace with” box, type the text you want to replace the found text with. In this scenario, you have to type the test.
- Click “Replace” to make changes or use “Replace All” to replace all instances at once.
Then, you’ll get the following table.
It is important to note that Excel wildcards can be used only in the Find box. You can’t use wildcards in both boxes of the Find and Replace function.
Using wildcards in Excel enables you to find and replace text more effectively, helping you to clean up and format your workbooks efficiently.
Final Thoughts
Using wildcards in Excel is like having a secret tool. To make the most of it, you need to know how to use it right. Take the time to learn the tricks and rules of using wildcards and it will streamline your data sorting challenges.
If you would like to learn how to perform a combination of conditional full and partial text replacements in Power Query, watch the below video.
Frequently Asked Questions
How do I use wildcards in Excel Find and Replace?
To use wildcards in Excel’s Find and Replace feature, go to the Home tab, click on Find & Select, and choose Replace.
In the Find what box, type the word or pattern with the wildcard (either * or ?).
In the Replace with box, type the replacement text.
Press Replace All or Replace as needed.
What are common examples of wildcard character searches in Excel?
In Excel, the two common wildcard characters are * (asterisk) and ? (question mark). Use the * to represent any number of characters, and the ? to represent a single character.
For example, searching for “s*t” will match “sat”, “set”, and “start”, while searching for “s?t” will match “sat” and “set” only.
How to apply wildcards in VLOOKUP?
To apply wildcards in the VLOOKUP function, use the wildcard characters * and ? in the lookup_value argument.
For example, =VLOOKUP(“s*t”,A2:B10, 2, FALSE) will search for the first value in column A that begins with “s” and ends with “t”, and return the matching value in column B.
Can you use wildcards in the COUNTIF function?
Yes, you can use wildcards in the COUNTIF function.
To do so, include the wildcard characters (* or ? ) in the criteria argument.
For example, =COUNTIF(A2:A10, “s*t”) will count the number of cells in the range A2:A10 that begin with “s” and end with “t”.
How to utilize wildcards in Excel’s conditional formatting?
To use wildcards in conditional formatting, select the cells you want to format, go to the Home tab, click on Conditional Formatting, choose New Rule, and select Format only cells that contain.
In the Format only cells with dropdown, choose Specific Text and enter the desired wildcard pattern.
Finally, click on Format to apply your desired formatting.
What to do when Excel wildcards are not working?
If Excel wildcards are not working, make sure you are using the correct characters (* or ?).
If your search text includes literal characters that are also wildcards, use the tilde (~) before them to treat them as regular characters.
For example, to search for the exact text string “*abc“, use the search pattern “~*abc“.
If the issue persists, double-check your formulas and functions for any errors or mistakes.
