Skip to main content
Back to path

Welcome to a Grasp lesson

Create a free account to keep studying and create your own study path.

Lesson 22

Wildcard Find and Replace for Bulk Data Correction

Recommended resources

Hello! In our previous lesson, we explored how to restructure data by splitting it across multiple columns using Flash Fill and Text to Columns. Today, we'll continue our journey into data preparation by focusing on how to correct and standardize information within cells.

As a freelancer, you'll often receive datasets with inconsistencies, typos, or unwanted characters. Manually fixing these issues is not scalable. This is where Excel's Find and Replace feature, supercharged with wildcards, becomes an indispensable tool. In this lesson, we will master its use for performing powerful, bulk data corrections. You'll learn how to find patterns, not just exact text, to clean up your data with speed and precision.

Getting Started: The Find and Replace Tool

Before we dive into wildcards, let's quickly review the basic Find and Replace functionality. This tool allows you to search for specific text or numbers in your worksheet and replace them with something else.

You can access it from the Home tab under Find & Select, but the keyboard shortcuts are far more efficient:

  • Ctrl + F opens the "Find" dialog.
  • Ctrl + H opens the "Replace" dialog.

To see a quick demonstration of a basic find and replace operation, please watch the first part of the following video.

Using Find and Replace and Wildcards in Excel

This video, "Using Find and Replace and Wildcards in Excel," provides a great starting point.

Watch the segment from the beginning until the start of the wildcard section. This will refresh your memory on how to perform an exact match replacement and use the "Replace All" command.

This standard replacement is useful, but its true power is unlocked when you need to find text that isn't an exact match. This is where wildcards come in.

Using Wildcards for Flexible Searches

Wildcards are special characters that act as placeholders for other characters in a search. They allow you to find items that have similar, but not identical, text. Excel primarily uses three wildcards: the asterisk (*), the question mark (?), and the tilde (~).

The Asterisk (*): Any Number of Characters

The asterisk is the most common wildcard. It represents any number of characters. For example, a search for Pro* would find "Product", "Project", and "Promotion".

Let's see this in action. The video below explains the concept and then we'll look at a powerful, practical application.

Using Find and Replace and Wildcards in Excel

Let's return to the "Using Find and Replace and Wildcards in Excel" video.

Please watch the segment explaining the asterisk from this timestamp. Notice how st*r finds words where any number of characters appear between "st" and "r".

A very common data cleaning task is removing extra information that has been added in parentheses, like "Client Name (Internal ID)" or "Product (Discontinued)". Using the asterisk, we can remove this text from an entire column in seconds. The search term would be (*) — this tells Excel to find an opening parenthesis, followed by any number of characters, followed by a closing parenthesis.

The next short video demonstrates this exact technique.

Master Data Cleaning Essentials on Excel in Just 10 Minutes

The video "Master Data Cleaning Essentials on Excel" by Kenji Explains shows a perfect real-world example.

Watch from the beginning of the demonstration. The presenter uses (*) in the "Find what" field and leaves the "Replace with" field blank to remove the parenthetical text entirely.

Here is an image showing the same process. You can see the (*) in the "Find what" field and the confirmation message after the replacement is complete.

Using `(*)` in the Find and Replace dialog allows you to find and remove text enclosed in parentheses, regardless of its length.

The Question Mark (?): Any Single Character

The question mark wildcard is more specific: it represents any single character. This is useful when you have minor variations in spelling or formatting. For example, a search for Sm?th would find both "Smith" and "Smyth". Similarly, FY-? would find "FY-A", "FY-B", and "FY-C", but not "FY-AB".

Let's watch the final segment from our first video to see this demonstrated.

Using Find and Replace and Wildcards in Excel

We'll once again use the "Using Find and Replace and Wildcards in Excel" video.

Watch the segment from this timestamp to understand how the question mark works as a single-character placeholder.

Advanced Techniques and Pro Tips

Now that you understand the two main wildcards, let's cover some crucial details that will elevate your data cleaning skills.

The Tilde (~): Finding a Literal Wildcard

What if you actually need to find an asterisk or a question mark in your data? If you type * into the "Find what" box, Excel will treat it as a wildcard.

To tell Excel you want to find the literal character, you must precede it with a tilde (~).

  • To find an asterisk, search for ~\*.
  • To find a question mark, search for ~?.
  • To find a tilde, search for ~~.

For a detailed explanation of all three wildcards, including the tilde, the following article is an excellent reference.

Excel wildcard: find and replace, filter, use in formulas

This article from Ablebits.com provides a clear summary of Excel's wildcard characters.

Please read the introductory section, starting from the beginning. This will give you a concise overview of the *, ?, and ~ wildcards.

Imagine you have a list of products with asterisks marking them for follow-up (e.g., Product A*), and you want to replace the asterisk with the word "Follow-up". You would use the tilde in your search.

To find and replace the asterisk character itself, you must use `~*` in the "Find what" field.

Ensuring Precision: "Match entire cell contents"

By default, Excel's Find and Replace looks for your search term anywhere inside a cell. A search for ??-?? could find AB-01 but also XYZ-AB-01-PDQ, because the latter string contains a match.

To find cells that only contain your search pattern, you must enable an additional option.

Excel wildcard: find and replace, filter, use in formulas

Let's return to the Ablebits.com article to see how to make our searches more precise.

Read the subsection How to search with wildcard. Pay close attention to the explanation and images demonstrating the "Match entire cell contents" checkbox. This option is critical for preventing accidental replacements in larger text strings.

This option is vital when you need to be certain you are only changing cells that exactly match your intended structure, a common requirement in professional data cleaning projects.

Conclusion

In this lesson, you've learned how to transform the standard Find and Replace tool into a sophisticated data cleaning engine using wildcards. This skill is fundamental for any Excel professional, allowing you to handle inconsistent and messy data efficiently.

Key Takeaways:

  • The asterisk (*) is a powerful wildcard representing any number of characters, perfect for tasks like removing text in parentheses (*).
  • The question mark (?) is a precise wildcard representing any single character, ideal for handling minor spelling variations or fixed-length codes.
  • The tilde (~) is a special character used to "escape" a wildcard, allowing you to find and replace literal asterisks (~\*) and question marks (~?).
  • For maximum precision, use the "Match entire cell contents" option to ensure you only replace cells that exactly match your search pattern.

The Find and Replace tool is perfect for interactive, one-time data cleaning tasks. However, if your source data changes, you would have to run the replacement again. In our next lesson, we'll explore a different approach: using Excel's text functions like TRIM, CLEAN, and SUBSTITUTE. These functions allow you to build dynamic, formula-based solutions that clean your data automatically as it updates.