Suppose you built a large worksheet and you now need to find every occurrence of a specific string of text or values in that worksheet. (In computerese, a string is defined as any continuous series of characters—text, numbers, math operators, or punctuation symbols.) You can use the Find & Select menu in the Editing group on the Home tab to locate any string, cell reference, or range name in cells or formulas on a worksheet. You can also find formatting with or without strings and then replace what you find with new strings, new formatting, or both.
When you click the Find command on the Find & Select menu (or press Ctrl+F), the Find And Replace dialog box appears, as shown in Figure 8-34. (If yours looks different, click Options to expand the dialog box.)
Use the options on the Find tab in the following ways:
Find What Type the string of characters you want to find. Be exact. Excel finds exactly what you type, including spaces—nothing more, nothing less.
Match Case Distinguish capital letters from lowercase letters, finding only those occurrences that match the uppercase and lowercase characters of the Find What string. If you leave this check box unselected, Excel disregards case.
Match Entire Cell Contents Find only complete and individual occurrences of the string. Ordinarily, Find searches for any occurrence of a string, even if it is part of another string.
Within Choose to search only the active worksheet or the entire workbook.
Search Choose to search by rows or by columns. Unless your worksheet is very large, your search takes place in the blink of an eye, so this option may not be much faster one way or the other. Finding items in a particular order may be desirable, however. When you select the By Rows option, Excel looks through the worksheet horizontally, row by row, starting with the currently selected cell. The By Columns option searches through the worksheet column by column, beginning with the selected cell.
Look In Choose formulas, values, or comments. The default is Formulas, which means all text and numeric entries, regardless of formatting, as well as formulas. When you select Values, Excel searches text and numeric entries and only the displayed results of formulas. When you select Comments, Excel examines only the text attached as a comment to a cell.
Note
If you want to search the entire workbook or worksheet to locate a string of characters (depending on the selection you make in the Within drop-down list), be sure that only a single cell is selected before clicking the Find command. Excel begins its search from that cell and travels through the entire worksheet or workbook. Select more than one cell before choosing Find, and the search is confined to the selected cells.
The nuances of the Look In options, Formulas and Values, can be confusing. Remember that the underlying contents of a cell and the displayed value of that cell are often not the same. When using these options, you should keep in mind the following:
If a cell contains a formula, the displayed value of the cell is usually the result of that formula.
If a cell contains a numeric value, the displayed value of the formatted cell may or may not be the same as the cell’s underlying value.
If a cell displays a text value, it is probably the same as the underlying value, unless the cell contains a formula that uses text functions.
If a cell has the General format, the displayed and underlying values of the cell are usually the same.
For example, if you type 1000 in the Find What text box and select Values as the Look In option, Excel looks at what is displayed in each cell. If you have an unformatted cell with the value 1000 in it, Excel finds it. If another cell has the same value formatted as currency ($1,000), Excel does not find it because the displayed value does not precisely match the Find What string. Because you’re searching through values and not formulas, Excel ignores the underlying content of the cell, which is 1000. If you select the Formulas option, Excel finds both instances, ignoring the formatting of the displayed values.
Note
If you close the Find And Replace dialog box and want to search for the next occurrence of the same string in your worksheet, you can press F4, the keyboard shortcut for repeating the last action (of any kind). Later, you can repeat your last search, even if you have performed other tasks since that search, by pressing Shift+F4.
Excel provides a way to find cells based on formatting in conjunction with other criteria, and even to find and replace specifically formatted cells, regardless of their content. If you click the Format button in the Find And Replace dialog box shown in Figure 8-35, the Find Format dialog box shown in Figure 8-36 appears. This dialog box has two names—Find Format and Replace Format—depending on whether you clicked the Format button that is adjacent to the Find What text box or the one adjacent to the Replace With text box on the Replace tab. Otherwise, the two dialog boxes are identical. You can select any number of options in this dialog box and click OK when you finish to add them to your criteria.
Figure 8-35. Click Choose Format From Cell to use the formatting of a selected cell as search criteria.
If you click the arrow button next to the Format button to display the Format menu, you can click Choose Format From Cell, as shown in Figure 8-35. Choose Format From Cell is also available as a button at the bottom of the Find Format (or Replace Format) dialog box shown in Figure 8-36.
When you click Choose Format From Cell, a small eyedropper appears next to the cursor when you hover over the worksheet area. Click a cell that is formatted the way you want, and the Find And Replace dialog box displays the word Preview* in the box that otherwise displays the message No Format Set. After you set your formatting criteria, Excel will not find any character strings you search for unless the formatting criteria also match. Without a string, Excel searches for the formatting only. For example, choosing the formatting of the selected cell—A3—in Figure 8-35 as the Find What criteria, clicking Find All would result in the selection of all the matching cells on the current worksheet. Or if you search for the word Sales and specify bold as a formatting criterion, Excel finds any cells containing the word Sales, but only if it is displayed in bold type. Excel also finds a cell containing the words Sales Staff with bold, italic, and underlined formatting because it contains both the word Sales and bold formatting, among other things. The more formatting options you set, the narrower the search.
Here are two things to watch out for. First, make sure the cell you use as an example does not have any nonapparent formatting applied, such as a number format in a cell displaying only text. Second, be sure to click Clear Find Format in the Format drop-down list shown in Figure 8-35 to remove the formatting criteria after you’re finished. Otherwise, you might not notice the word Preview* in the dialog box, and future searches could produce unexpected results.
You can use the wildcard characters ? and * to widen the scope of your searches. Wildcard characters are helpful when you’re searching for a group of similar but not identical entries or when you’re searching for an entry you don’t quite remember. Use them as follows:
The ? character takes the place of any single character in a Find What string. For example, the Find What string 1?0 matches the values 1000, 190, 1900, 100A, Z1R0, and so on. (This finds any entry that contains the search string, even if the entry is part of a larger string.)
The * character takes the place of zero or more characters in a Find What string. For example, the string 12* matches the entries 12, 120, 125, 1200000, and even 123 Maple Street.
You can use the wildcard characters anywhere within a Find What string. For example, you can use the string *s to find all entries that end with s. Alternatively, you can use the string *es* to find each cell that contains the string sequence es anywhere in its formula or value.
To search for a string that actually contains a wildcard character (? or *), type a tilde (~) preceding the character. For example, to find the string Who? (including the question mark), type Who~? as your Find What text.
Replace works much like Find—in fact, they open the same dialog box. When you click Replace on the Find & Select menu on the Home tab (or press Ctrl+H), you see a dialog box like the one in Figure 8-37. (If yours looks different, click Options to expand the dialog box.)
For example, to replace each occurrence of the name Joan Smith with John Smith, type Joan Smith in the Find What text box and John Smith in the Replace With text box. You can also find and replace formats using the dual Format buttons. For example, you could search for every occurrence of 14-point bold and italic Times Roman and replace it with 12-point, double-underlined Arial.
To replace every occurrence of a string or formatting, click Replace All. Instead of pausing at each occurrence to let you change or skip the current cell, Excel locates all the cells containing the Find What string and replaces them.
Figure 8-37. You can find and replace character strings and formats by clicking the Replace command on the Find & Select menu.
The Expanding Dialog Box
The Find And Replace dialog box does a neat trick. After you execute a search, the dialog box expands to list all the cells with contents that match your criteria:
When you select an entry in the list, Excel jumps to that location and selects the cell. You can drag the bottom border of the dialog box to see more of the list. This feature makes it much easier to do extensive find-and-replace tasks because you can see the list at all times; because items remain in the list even after you execute a replacement operation; and because you can keep the dialog box open, change worksheets or workbooks, and utilize the Undo button while you work.