How to Find and Replace Using Wildcards in Excel

If we need to change cell values in bulk, we use the Find and Replace dialog box. However, sometimes we may need to change cell values with partial match. We can easily use Wildcards to Find and Replace those cell values. In this article, we will learn how to use wildcards to Find and Replace cell values in Excel.


📁 Download Excel File

Download the following Excel workbook to realize the topic more clearly.


What Are Wildcards in Excel?

A wildcard is a particular type of character in Excel that can replace any other character. Especially, if you do not know a definite character, you can substitute a wildcard. Excel has 3 wildcards such as Asterisk (*), Question mark (?), and Tilde (~) symbols.


Learn to Find and Replace in Excel with These 3 Wildcards

We will discuss how to Find and Replace using the available 3 wildcard characters with the help of the following dataset.


Approach

1. Using Asterisk Wildcard (*)

We can use the Asterisk wildcard to Find and Replace any amount of characters. Let’s see how we can use the Asterisk symbol to Find and Replace in Excel.

  • s*t can Find and Replace the words start, started, student.
  • *st can find the words trust, frost, lost.
  • st* can be used to match star, steward, steps.

⬇️⬇️ STEPS ⬇️⬇️

  • In the beginning, let me show you how to open the Find and Replace dialog box.
  • Under the Home tab, click Find & Select in the Editing group.

  • From the menu, you can either select Find or Replace.

  • Momentarily, the Find and Replace dialog box will appear on the screen. Or, you can just press Ctrl+F concurrently to open the dialog box.

  • Next, we will use the Asterisk symbol to Find and Replace Years starting with 20.
  • Now, from the Find tab, type 20* in the Find what box and click Find All.

find and replace using asterisk wildcards in excel

  • Momentarily, the dialog box will show the Years starting with 20 including the workbook, sheet, and even cell numbers and values.

  • Afterward, if you want to replace the Years with something click the Replace tab.
  • Type in the value you want to display in the Replace with box and click Replace All.

applying find and replace using asterisk wildcard in excel

  • A tiny dialog box will show how many changes were applied. Click OK and Close the Find and Replace dialog box.

  • You can see the results in the following screenshot.

utilizing find and replace using asterisk wildcard in excel

📕 Read More: 5 Tricks to Insert Comma Between Words in Excel


Approach

2. Utilizing Question Mark Wildcard (?)

Unlike the Asterisk symbol, a Question mark wildcard can only find one character. However, we can use multiple question marks to find multiple characters. For instance:

  • so?t will locate sort, soft.
  • wi?? can find wire, wild, wipe.

⬇️⬇️ STEPS ⬇️⬇️

  • Initially, press Ctrl+F simultaneously to open the Find and Replace dialog box.
  • We want to Find and Replace Canada-Mexico-USA from our dataset. Just like we discussed, we need to put a Question mark for every character.
  • Under the Find tab, type in ??????-??????-??? And click Find All.

implementing find and replace using question mark wildcard in excel

  • Excel will find the one cell that accommodates Canada-Mexico-USA.

  • Later, to replace the value with North America, we navigate to the Replace tab, enter North America in the Replace with box and click Replace All.

find and replace using question mark wildcard in excel

  • We have replaced Canada-Mexico-USA with North America. Great!

Note: If you write ??????- in the Find what box, Excel will find the cell containing Canada-Mexico-USA. However, if you want to replace the value with North America, it will appear twice in that cell like North AmericaNorth AmericaUsa. Since Canada and Mexico both have 6 letters, Excel finds both of them in one cell and changes both of them with North America.

📕 Read More: 3 Quick Ways to Replace Text After Specific Character


Approach

3. Executing Tilde Wildcard (~)

Tilde wildcard can find the wildcards asterisk (*), question mark (?), and even tilde (~) in the whole workbook. For example:

  • what~? finds what?
  • *~** can find an asterisk like stop*, st*op.
  • ~~ will find cells containing a tilde (~).
  • *~? finds any cell value that ends with a question mark i.e. how?, hello?.

⬇️⬇️ STEPS ⬇️⬇️

  • First of all, press Ctrl+F together to summon the Find and Replace dialog box.
  • Next, we want to find the cells containing the asterisk symbol.
  • Write *~** in the Find what box and click Find All. Here, the third asterisk is the one we are looking for. The rest are used as wildcards to find the asterisks.

utilizing find and replace using tilde wildcard in excel

  • Excel will find the cells containing the asterisk symbol in a jiffy.

  • At this time, type TBD in the Replace with box under the Replace tab and click Replace All to replace the cell value accommodating the asterisk symbol.

find and replace using tilde wildcard in excel

  • Just like in the picture below, the cell value has changed to TBD.

  • Similarly, to find the question mark in the dataset, type ~? in the Find what box.

📕 Read More: 7 Ways to Find and Replace Multiple Values in Excel


📝  Takeaways from This Article

📌  Firstly, we learned how to use Asterisk Wildcard to Find and Replace multiple characters.

📌  Secondly, we discussed the role of the Question mark wildcard to find and replace characters.

📌  Finally, we illustrated the use of the Tilde wildcard to find and replace the wildcards themselves.


Conclusion

That concludes the discussion for today. These are some convenient methods to Find and Replace using wildcards in Excel. If you have any queries or recommendations, kindly notify us in the comments section. Downloading the practice sheet will help you comprehend the concepts better. Visit our website ExcelDen to find out about diverse kinds of Excel methods. We appreciate you taking the time to read this article.


Related Articles

(Visited 111 times, 1 visits today)
Nazmus Sakib

Nazmus Sakib

Hello, and thanks for visiting my profile. Right now I am researching Microsoft Excel, thus I will be sharing articles about myriad ways to utilize Excel effectively here. I love to watch movies and TV series in my free time. I am also a huge football fan. My priority is to continually broaden my horizons in order to enrich my work.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo