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, click Find or Replace item from the Find & Select drop-down menu. To enable the menu, add the Editing ribbon group. See the Create a Simple Spreadsheet Application topic for a demonstration on how to provide a Ribbon UI for the SpreadsheetControl.
End-users can also invoke the dialog using the following short keys:
- CTRL+F - to invoke the dialog with the Find tab selected.
- CTRL+H - to invoke the dialog with the Replace tab selected.
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 with the following advanced options to fine-tune a search:
| Option | Description |
|---|---|
| Search | Specifies the search direction. 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 | Enables 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, end-users 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.
Clicking the Replace button replaces only the value of the selected matching cell. Click 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.
Tip
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.