Microsoft Office Tutorials and References
In Depth Information
Tip 71: Performing Inexact Searches
Wildcard characters also work with values. For example, searching for 3* locates all cells that contain
an entry that begins with 3. Searching for 1?9 locates all three-digit entries that begin with 1 and end
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):
➤ 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.
➤ 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.
➤ 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/*/2013 finds all
dates in October 2013, 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.