- 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.
For more resources for Microsoft Excel: