Excel: Use Find to Find an Asterisk

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

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?

  1. 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+~".

  2. Use ~* to really find an asterisk.

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

  3. 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.