Using Find and Replace

Excel's Find and Replace feature enables you to easily update the contents of a single worksheet or all the worksheets in a workbook on either a case-by-case basis or globally. To make quick and easy editing changes with this feature, follow these steps:

1. To perform a search and replace through the entire worksheet, select a single cell. To restrict the search-and-replace operation to a specific cell range or non-adjacent selection, select all the cells to be edited.
   
   
2. Choose Edit > Replace or press Ctrl+H to open the Find and Replace dialog box.
   
   
   
   
3. Click the Options button to expand the Replace tab shown below.
   
   
   
   
4. Type the search string that you want to locate in the Find What drop-down list box and specify any formatting to be searched by clicking its Format button.
   
   When entering the search string, you can use the question mark (?) or asterisk (*) wildcards to stand for any characters that you are unsure of. Use the question mark to stand for a single character as in Jos?ph, which matches either Joseph. Use the asterisk to stand for multiple characters as in 9*1, which locates 91, 94901, or even 9553 1st Street. To search for a wildcard character, precede the character with a tilde (~), as in ~*2.5, to locate formulas that are multiplied by the number 2.5. (The asterisk is the multiplication operator in Excel.)
   
   If the cell holding the search string that you are looking for is formatted in a particular way, you can narrow the search by specifying what formatting to search for. When you click the Format button, Excel opens a Find Format dialog box with the same tabs and options as the standard Format Cells dialog box. Select the formatting that you want to search for in this dialog box and then click OK.
   
   
5. Type the replacement string in the Replace With drop-down list box and specify any formatting to be added to the replacement string by clicking its Format button.
   
   
6. Select any additional options you want:
   
   

   Within:
   Select the Workbook setting to search all the worksheets within a workbook.
   
   Search:
   Change this setting from By Rows to By Columns to search down the columns and across the rows rather that across the rows and then down the columns.
   
   Look In:
   By default, Excel selects Formulas for this option to look for the search string in the contents of each cell as it's displayed on the Formula bar. To have Excel search for the string in among the values displayed in the cells themselves, select Values on this dropdown list. To have the program look for the search string only in the comments added to the cells, select Comments on this drop-down list.
   
   Match Case:
   Find occurrences of the search string only when it matches the case that you entered.
   
   Match Entire Cell Contents:
   Find occurrences of the search string only when it matches the entire cell entry.
   
   By default, Excel considers any occurrence of the search string to be a match - even when it occurs as part of another part of the cell entry. This means that when you search for 25, Excel considers cells containing 25, 15.25, 25 Main Street, and 250,000 as matches. Select the Match Entire Cell Contents check box to match only complete occurrences of your search string.
   
   

7. Click the Find Next button to locate the first occurrence of the search string. When Excel finds an occurrence, click the button to replace the first occurrence with the replacement string or the Find Next button again to skip this occurrence.
   
   Using the Find Next and Replace buttons to search and replace on a case-by-case basis is by far the safest way to use the Find and Replace feature. If you are certain (really certain) that you won't mess anything up by replacing all occurrences throughout the spreadsheet, click the Replace All button to have Excel make the replacements globally without stopping to show you which cells are updated.
   
   
8. When you finish replacing entries on a case-bycase basis, click the Close button to close the Find and Replace dialog box.
   
   
9. When you finish replacing entries on a case-bycase basis, click the Close button to close the Find and Replace dialog box.

Note that if you globally replace the search string in the worksheet, Excel automatically closes the Find and Replace dialog box when it finishes replacing the last match.

Be clear about the difference between the Formulas and Values Look In options in the expanded Find and Replace dialog box. When, for example, the default Formulas option is selected and you enter 15 the search string, Excel looks for these two digits only in text entries and within the contents of formulas as they appear on the Formula bar (as in =15+A4). To have the program find the digits 15 when directly entered in a cell or returned as the result of a formula calculation as actually displayed in the cells of the worksheet (as when the formula =A2-A3 returns 15 to a cell), you must select Values as the Look In option before you conduct the search.