Find and Replace
The Find and Replace dialog displays when the Data Tools | Find | Find, Data Tools | Find | Find Next, or Data Tools | Find | Replace commands are clicked. The Find and Replace dialog is used to search for and replace specific text in the worksheet.
The Find Page
The Data Tools | Find | Find and Data Tools | Find | Find Next commands open the Find page of the Find and Replace dialog.
Search for and replace specific text in the
worksheet with the Find
and Replace dialog.
Find
To find a word or phrase, type the text you want to search for in the Find field. Click the arrow at the right to select from a list of the most recently used text strings. The asterisk * and question mark ? wildcards can be used in the Find box. Click the arrow at the right to select from a list of the most recently used criteria.
-
A question mark ? finds a single character in the specified location. For example, 200? finds 2009, 2008, 200a, etc.
-
An asterisk * finds any number of characters at the specified location. For example, *01 finds 601, 1201, c01, etc.
In
Next to In, choose the parameters of the search from the list. Choices include The column where active cell is, The row where active cell is, and The entire limits.
-
Select The column where the active cell is to search only the column (i.e. column B) of the active cell (i.e. cell B2) for the information listed in the Find field.
-
Select The row where active cell is to search only the row (i.e. row _2) of the active cell (i.e. cell B2) for the information listed in the Find field.
-
Select The entire limits to search the entire worksheet for the information listed in the Find field.
Search Order
The Search order controls the direction of the search: down through columns by selecting By columns or to the right across rows by selecting By rows.
In this example, cell A1 is selected. If the Find criteria
is "7",
and By Column is the Search
order, cell A5 is found first.
If By row is
the search order, cell B1 is found first.
Match Case
If you have case sensitive characters in the Find text string, check the Match case check box. Selecting Match case distinguishes between uppercase and lowercase characters. For example, a search for "Elevation" with the Match case option selected will not find entries for "elevation", but will find entries for "Elevation".
Deselect All First
Check the Deselect all first box to deselect all selected cells before performing the search. All previously selected cells will be deselected prior to the search when the Deselect all first check box is checked. If the Deselect all first box is deselected, the results of a previous search will remain highlighted when performing the next search.
Method
Choose the search Method from the list to determine how the search is performed.
This examples assume "Golden, CO" is in the Find field.
-
Select Cell matches target exactly to require that the exact criteria in the Search box is present in a cell before it is selected. For example, only cells that have exactly "Golden, CO" will be selected.
-
Select Cell contains target phrase to require that the phrase in the Search box is present in a cell before it is selected. For example, cells that have "Golden CO", "Golden Company", or "Golden Colorado" will be selected.
-
Select Cell contains all of the target words to require that all of the Search criteria words are present in a cell before it is selected. For example, cells that have "Golden" and "CO" somewhere in the cell (i.e. "Golden is the best city in Colorado" will be selected).
-
Select Cell contains any of the target words to require that any of the Search criteria words are present in a cell before it is selected. For example, cells that have "Golden is a city" or "CO is a state" will be selected.
Find All Button
Click the Find All button to find all occurrences of the Find criteria in the worksheet. All of the cells that contain the Find criteria will be highlighted.
Find Next Button
Click the Find Next button to find the next occurrence of the characters specified in the Find box. This allows you to meet the criteria one at a time. The next instance of the Find criteria will be highlighted.
Close Button
Click Close to exit the Find and Replace dialog.
The Replace Page
The Data Tools | Find | Replace command opens the Replace page of the Find and Replace dialog. The Replace page has all of the Find page fields, with the addition of the Replace with field. The Replace page, Method field has only two options.
Replace numbers or text in the worksheet
with the Find
and Replace dialog.
Replace With
Type the text you want to replace in the Find box. To delete the characters in the Find box from your worksheet, leave the Replace with box blank. Click the arrow at the right to select from a list of the most recently searched items.
Method
Choose the search Method from the list to determine how the search is performed.
The examples assume "Golden, CO" is in the Find field.
-
Select Cell matches target exactly to require that the exact criteria in the Search box is present in a cell before it is selected. For example, only cells that have exactly "Golden, CO" will be selected.
-
Select Cell matches target exactly to require that the exact criteria in the Search box is present in a cell before it is selected. For example, only cells that have exactly "Golden, CO" will be selected.
-
Select Cell contains target phrase to require that the phrase in the Search box is present in a cell before it is selected. For example, cells that have "Golden CO", "Golden Company", or "Golden Colorado" will be selected.
Replace Button
Click the Replace button to replace the selected occurrence of the criteria in the Find box with the criteria in the Replace with box, find the next occurrence of the criteria in the Find box, and then stop. If you want to automatically replace all occurrences of the search criteria in the worksheet, click the Replace All button.
Replace All Button
Click the Replace All button to replace all occurrences of the Find criteria in your document with the Replace with criteria. If you want to review and selectively replace each occurrence, click the Replace button.