Microsoft Office Tutorials and References
In Depth Information
Performing Inexact Searches
To search for a question mark or an asterisk, precede the character with a tilde
character (~). For example, the following search string finds the text *NONE* :
If you need to search for the tilde character, use two tildes.
If your searches don’t seem to be working correctly, double-check these three options (which
sometimes have a way of changing on their own):
h Match Case: If this check box is selected, the case of the text must match exactly. For
example, searching for smith does not locate Smith .
h Match Entire Cell Contents: If this check box is selected, a match occurs if the cell
contains only the search string (and nothing else). For example, searching for Excel doesn’t
locate a cell that contains Microsoft Excel .
h Look In: This drop-down list has three options: Values, Formulas, and Comments. If, for
example, Values is selected, searching for 900 doesn’t find a cell that contains 900 if that
value is generated by a formula.
Remember that searching operates on the selected range of cells. If you want to search the entire
worksheet, select only one cell before you begin your search.
Also, remember that searches do not include numeric formatting. For example, if you have a
value that uses currency formatting so that it appears as $54.00, searching for $5* doesn’t
locate that value.
Working with dates can be a bit tricky because Excel offers many ways to format dates. If you
search for a date by using the default date format, Excel locates the dates even if they’re
formatted differently. For example, if your system uses the m/d/y date format, the search string
10/*/2010 finds all dates in October 2010, regardless of how the dates are formatted.
You can also use an empty Replace With field. For example, to quickly delete all asterisks from
your worksheet, enter in the Find What field and leave the Replace With field blank. When you ~*
click the Replace All button, Excel finds all the asterisks and replaces them with nothing.