Find and Replace Dialog
- 3 minutes to read
The Find and Replace dialog allows end-users to perform a search in the active worksheet and optionally replace the search term with another value. This dialog box includes two tabs: Find and Replace.
To invoke the Find and Replace dialog, on the Home tab, in the Editing group, click Find & Select.
In the button’s drop-down menu, select one of the following.
- Click Find (or press CTRL+F) to invoke the dialog with the Find tab selected.
- Click Replace (or press CTRL+H) to invoke the dialog with the Replace tab selected.
For an example on how to provide a Ribbon UI for the SpreadsheetControl, refer to the Getting Started topic.
Find Tab
End-users can use the Find tab to search for information in the active worksheet.
To start a search, an end-user should enter the search term in the Find what field, and then click the Find Next button. The Find tab provides end-users the capability to use advanced options to fine-tune a search.
The search options located on the Find tab are listed in the table below.
| Option | Description |
|---|---|
| Search | Allows end-users to specify the direction of the search. Select By Rows to search each row from left to right, or By Columns to search each column from top to bottom. |
| Look in | Allows end-users to select what to examine in each cell when searching. Select Formulas to search for cell values and formula expressions, excluding the calculated results, or Values to search for cell values only (including values calculated from formulas). |
| Match case | Allows end-users to perform a case-sensitive search. |
| Match entire cell contents | Allows end-users to search for a complete match of the search term. |
Replace Tab
End-users can use the Replace tab to replace the data they are searching for with another value.
To replace a value, the end-user should enter the search term in the Find what field and the replacement text for this search term in the Replace with field, and then click the Find Next button to find the first occurrence of the search term. Next, the end-user can click the Replace button to replace only the value of the selected matching cell, or Replace All to replace all occurrences of the search term.
The Replace tab contains the same advanced options as the Find tab. The difference is that only the Formulas option is available for end-users in the Look in box.
To perform a search in code, use the CellRange.Search, Worksheet.Search or IWorkbook.Search method, depending on where you wish to search for your data: in the specific cell range, the current worksheet or the entire document. To set options affecting a search, create an instance of the SearchOptions class and pass it as a parameter to the Search method. For an example, refer to the Find and Replace document.