Most valuable professional
  • Excel Book Excerpt

Excel Use Find to Find an Asterisk

This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

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?


Figure 693 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+~”.


Figure 694 Use ~* to really find an asterisk.

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


Figure 695 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.

<-Previous Topic                    Next Topic->

For more resources for Microsoft Excel: