Suppose you've 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
is defined as any continuous series of
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.)
Figure 8-34: Use the Find tab to locate a character string.
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 will find exactly what you type, including spaces-nothing more, nothing less.
Distinguish capital letters from lowercase letters, finding only those occurrences that exactly match the uppercase and lowercase characters of the Find What string. If you leave this check box
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 Search only the active worksheet or the entire workbook.
Search by rows or by columns. Ordinarily, your search will take place in the blink of an eye either way, but use this option if you have a large spreadsheet and have some clue where to look. When you select the By Rows option, Excel looks through the worksheet horizontally, row by row, starting with the currently selected cell. Select this option if you think the string is located to the right of the selected cell. The By
Look In Choose formulas, values, or comments. When you click Formulas, Excel searches only in formulas. When you select Values, Excel searches any constant values as well as the displayed results of formulas. When you select Comments, Excel examines only text attached as a comment to a cell.
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), select a single cell before clicking the Find command. Office Excel 2007 begins its search from that cell and
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 that the underlying content of the cell is 1000. If you select the Formulas option, Excel finds both instances, ignoring the formatting of the displayed values.
If you close the Find And Replace dialog box and want to search for the
Excel provides a way to find cells based on formatting in conjunction with other criteria, and even to find and replace
Figure 8-35: Click Choose Format From Cell to use the formatting of a selected cell as search criteria.
Figure 8-36: Click Format in the Find And Replace dialog box to display the Find Format dialog box.
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.
On the CD You'll find the 2008 New Projections. xlsx file in the Sample Files section of the companion CD.
When you click Choose Format From Cell, the Find Format (or Replace Format) dialog box disappears, and a small eyedropper appears next to the cursor. Click a cell that is formatted the way you want, and the Find And Replace dialog box reappears with the word
in the box that otherwise displays the message
No Format Set.
After you set your formatting criteria, Excel will not find the character strings you search for unless the formatting criteria also match. For example, if you search for the word
and specify bold type as a formatting criterion, Excel finds any cells containing the word
in bold type. Excel will find a cell containing the words
with bold, italic, and
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
The ? character takes the place of any single character in a Find What string. For example, the Find What string 100? matches the values 1000, 1001, 100A, 100B, and so on.
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
To search for a string that actually contains a wildcard character (? or *), type a tilde (~)
Replace works much like Find-in fact, they
Figure 8-37: You can find and replace character strings and formats by clicking the Replace command on the Find & Select menu.
For example, to replace each occurrence of the name Joan Smith with John Smith, type
in the Find What text box and
in the Replace With text box. You can also find and replace formats using the dual Format
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
Although you can use wildcards in the Find What box to aid in your search, if you type wildcard characters in the Replace With box, Excel uses a literal ? or * symbol when it replaces each occurrence of your Find What text.
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