MrExcel.com - Your One Stop for Excel Tips & Solutions

Use Find to Find an Asterisk

October 18, 2022 - by

Use Find to Find an Asterisk

Problem: My largest customer is Wal*Mart. When I use Find or Find and Replace to search for Wal*Mart, Excel also finds Wallingsmart. I know this happens because Excel sees * as a wildcard character. What if I really want to search for an asterisk?

When you use Find and Replace to look for Wal*Mart, it will find Wallingsmart because the asterisk is treated as a wildcard.
Figure 753. The asterisk is a wildcard.

Strategy: You can use three wildcard characters in the Find and Replace dialog: *, ?, and ~.

If you include an *, Excel will search for any number of characters where the asterisk is located. For example, searching for Wal*mart will find Wal*mart and also Walton Williams is smart.

If you include a ?, Excel will search for any one character. For example, searching for ?arl will find both Carl and Karl.

To force Excel to search for an asterisk, tilde, or a question mark, you can precede the wildcard with a tilde (~). When you search for Wal~*mart, Excel will only find Wal*mart. If you search for Who~? Excel will only find Who? and not Whom. When you search for “Alt+~~”, Excel will find “Alt+~”.

If you only want to find Wal*Mart, search for WAL Tilde Asterisk MART. The Tilde tells Excel that you are really looking for an asterisk character. If you are really looking for a question mark, use Tilde Question Mark. If you really have to look for a tilde, search for Tilde Tilde.
Figure 754. Use ~* to really find an asterisk.

Additional Details: To change all the multiplication formulas to division formulas, you can have Excel change all ~* to /.

In the Find & Replace dialog, search for Tilde Asterisk and replace with slash.
Figure 755. Change multiply to divide.

Gotcha: Changing a formula of =5*3 to =5/3 will work fine. Changing a math exercise sheet with 5*3 to become 5/3 might change your values to May 3rd. Use caution when changing asterisk to slashes within text.

This article is an excerpt from Power Excel With MrExcel

Title photo by Víctor Martín on Unsplash