Microsoft Office Tutorials and References
In Depth Information
Tip 71: Performing Inexact Searches
Performing Inexact Searches
If you have a large worksheet with lots of data, locating what you’re looking for can be difficult. The
Excel Find and Replace dialog box is a useful tool for locating information, and it has a few features
that many users overlook.
Access the Find and Replace dialog box by choosing Home➜Editing➜Find & Select➜Find (or by
pressing Ctrl+F). If you’re replacing information, you can use Home➜Editing➜Find & Select➜Replace
(or Ctrl+H). The only difference is which of the two tabs is displayed in the dialog box. Figure 71-1
shows the Find and Replace dialog box after clicking the Options button, which expands the dialog
box to show additional options.
Figure 71-1: The Find and Replace dialog box with the Find tab selected.
In many cases, you want to locate “approximate” text. For example, you may be trying to find data for
a customer named Stephen R. Rosencrantz. You can, of course, search for the exact text: Stephen R.
Rosencrantz. However, there’s a reasonably good chance that the search will fail. The name may have
been entered differently, as Steve Rosencrantz or S.R. Rosencrantz, for example. It may have even
been misspelled as Rosentcrantz.
The most efficient search for this name is to use a wildcard character and search for st*rosen* and
then click the Find All button. In addition to reducing the amount of text that you enter, this search is
practically guaranteed to locate the customer, if the record is in your worksheet. The search may also
find some records that you aren’t looking for, but that’s better than not finding anything.
The Find and Replace dialog box supports two wildcard characters:
➤ ? matches any single character.
➤ * matches any number of characters.