5.2. Find and Replace
When you're dealing with great mounds of data, it can be tough to ferret out the nuggets of data you need. Fortunately, Excel's find feature is great for helping you locate numbers or text, even when they're buried within massive workbooks holding dozens of complex worksheets. And if you need to make changes to a bunch of identical items, the find-and-replace option can be a real time-saver.
The find and replace feature includes both simple and advanced options. In its basic version, you're only a quick keystroke combo away from a word or number you know is lurking somewhere in your data pile. With the advanced options turned on, you can do things like search for cells that have certain formatting characteristics and apply changes automatically. The next few sections dissect these features.
Excel's find feature is a little like the Go To tool described in Chapter 1, which lets you move across a large expanse of cells in a single bound. The difference is that Go To moves to a known location, using the cell address you specify. The find feature, on the other hand, searches every cell until it finds the content you've asked Excel to look for. Excel's search works similarly to the search feature in Microsoft Word, but it's worth keeping in mind a few additional details:
Excel searches by comparing the content you enter with the content in each cell. For example, if you searched for the word Date , Excel identifies as a match a cell containing the phrase Date Purchased .
When searching cells that contain numeric or date information, Excel always searches the display text . (For more information about the difference between the way Excel displays a numeric valuethe underlying value Excel actually stores see Sidebar 2.1.)
For example, say a cell displays dates using the day-month-year format, like 2-Dec-05 . You can find this particular cell by searching for any part of the displayed date (using search strings like Dec or 2-Dec-05 ). If you use the search string 12/2/2005 you won't find a match, because the search string and the display text are different. A similar behavior occurs with numbers. For example, the search strings $3 and 3.00 will match the currency value $3.00 . However, the search string 3.000 won't turn up anything, because Excel won't be able to make a full text match.
Excel searches one cell at a time, from left-to-right . When it reaches the end of a row, it moves to the first column of the next row.
To perform a find operation, follow these steps:
Move to the cell where you want the search to begin.
If you start off halfway down the worksheet, for example, the search covers the cells from there to the end of the worksheet, and then " loops over" and starts at cell A1. If you select a group of cells, Excel restricts the search to just those cells. You can search across a set of columns , rows, or even a non-contiguous group of cells.
Choose Edit Find, or press Ctrl+F.
The Find and Replace window appears, with the Find tab selected.
In the "Find what" combo box, enter the word, phrase, or number you're looking for.
If you've performed other searches recently, you can reuse these search terms. Just choose the appropriate search text from the "Find what" drop-down list.
Click Find Next.
Excel jumps to the next matching cell, which becomes the active cell. However, Excel won't highlight the matched text or in any way indicate why it decided the cell was a match. If it doesn't find a matching cell, Excel displays a message box telling you it couldn't find the requested content.
If the first match isn't what you're looking for, you can keep looking by clicking Find Next again to move to the next match. Keep clicking Find Next to move through the worksheet. When you reach the end, Excel resumes the search at the beginning of your worksheet, potentially bringing you back to a match you've already seen. When you're finished with the search, click Close to dismiss the Find and Replace window.
| GEM IN THE ROUGH |
The Find All Feature
One of the problems with searching in Excel is that you're never quite sure how many matches there are in a worksheet. Sure, clicking Find Next gets you from one cell to the next, but wouldn't it be easier for Excel to let you know right away how many matches it's found?
Enter the Find All feature. With Find All, Excel searches the entire worksheet in one go, and compiles a list of matches. In the example shown here, the search for "Price" matched three cells in the worksheet. The list shows you the complete text in the matching cell and the cell reference (for example, $B$2, which is a reference to cell B2).
The Find All button doesn't lead you through the worksheet like the find feature. It's up to you to select one of the results in the list, at which point Excel automatically moves you to the matching cell.
The Find All list won't automatically refresh itself: after you've run a Find All search, if you add new data to your worksheet, you need to run a new search to find any newly added terms.
However, Excel does keep the text and numbers in your found-items list synchronized with any changes you make in the worksheet. For example, if you change cell D5 to "Total Price," the change appears in the Value column in the found-items list automatically . This is a great tool for editing a worksheet because you can keep track of multiple changes at a single glance.
Finally, the Find All feature is the heart of another great Excel guru trick: it gives you another way to change multiple cells at once. After you've performed the Find All search, select all the entries you want to change from the list by clicking them while you hold down Ctrl (this allows you to select several at once). Then, click in the Formula Bar and start typing the new value. When you're finished, hit Ctrl+Enter to apply your changes to every selected cell. Voil it's like Find and Replace, but you're in control!
Basic searches are fine if all you need to find is a glaringly unique phrase or number ( Pet Snail Names or 10,987,654,321 ). But Excel's advanced search feature gives you lots of ways to fine-tune your searches or even search more than one worksheet. To conduct an advanced search, begin by clicking the Options button in the Find and Replace window, as shown in Figure 5-9.
You can set any or all of the following options:
If you want your search to span multiple worksheets, choose Workbook in the Within box. The standard option, Sheet, searches all the cells in the currently active worksheet. If you want to continue the search in the other worksheets in your workbook, choose Workbook. Excel examines the worksheets from left to right. When it finishes searching the last worksheet, it loops back and starts examining the first worksheet.
The Search pop-up menu lets you choose the direction you want to search. The standard option, By Rows, completely searches each row before moving on to the next one. That means that if you start in cell B2, Excel searches C2, D2, E2, and so on. Once it's moved through every column in the second row, it moves onto the third row and searches from left to right.
On the other hand, if you choose By Columns, Excel searches all the rows in the current column before moving to the next column. That means that if you start in cell B2, Excel searches B3, B4, and so on until it reaches the bottom of the column and then starts at the top of the next column (column C).
The "Match case" option lets you specify whether capitalization is important. If you select "Match case" Excel only finds words or phrases whose capitalization matches. Thus, searching for Date matches the cell value Date but not dat e.
The "Match entire cell contents" option lets you restrict your searches to the entire contents of a cell. Excel ordinarily looks to see if your search term is contained anywhere inside a cell. So if you specify the word Price , Excel finds cells containing text like Current Price and even Repriced Items . Similarly, numbers like 32 match cell values like 3253, 10032 , and 1.321 . Turning on the "Match entire cell contents" option forces Excel to be precise.
| POWER USERS' CLINIC |
Sometimes you sorta, kinda know what you're looking forfor example, a cell with some version of the word "date" in it (as in "date" or "dated" or " dating "). What you really need is a search tool that's flexible enough to keep its eyes open for results that are similar but not exactly alike. Power searchers will be happy to know that Excel lets you use wildcards in your searches. Wildcards are search symbols that let you search for variations on a word.
The asterisk (*) wildcard represents a group of one or more characters . For example, a search for s*nd finds any word that begins with the letter s and ends with the letters nd ; for example, it would find words like sand , sound , send , or even the bizarre series of characters sgrthdnd . The question mark ? wildcard represents any single character. For example, a search for f?nd turns up find or fund but not friend .
Wildcards are particularly useful when you're using the "Match entire cell contents" option. For example, if you turn on the "Match entire cell contents" option and enter the search term date* you'll find any cell that starts with the word date . In contrast, if you performed the same search without turning the "Match entire cell contents" option on, you'd find any cell containing the word date .
If you happen to want to search for special characters like the asterisk or the question mark you'll need to use the tilde (~) before the wildcard. For example, the search string ~* searches for cells that contain the asterisk symbol.
Excel's find and replace is an equal opportunity search tool: it doesn't care what the contents of a cell look like. But what if you know, for example, that the data you're looking for is formatted in bold, or that it's a number that uses the Currency format? You can use these formatting details to help Excel find the data you want and ignore cells that aren't relevant.
To use formatting details as part of your search criteria, follow these steps:
Launch the Find tool.
Choose Edit Find, or press Ctrl+F. Make sure that the Find and Replace window is showing the advanced options (by clicking the Options button).
Click the Format button next to the "Find what" search box.
The Find Format dialog box appears (Figure 5-10), which contains the same options as the Format Cell dialog box discussed throughout Chapter 4.
Specify the format settings you want to look for.
Using the Find Format dialog box, you can specify any combination of number format, alignment, font, fill pattern, borders, and formatting. Chapter 4 explains all these formatting settings in detail. You can also search for protected and locked cells, which are described in Chapter 15.
When you're finished, click OK to return to the Find and Replace window.
Next to the "Find what" search box, a preview appears indicating the formatting of the cell that you'll be searching for, as shown in Figure 5-11.
To remove these formatting restrictions, click the pop-up menu to the right of the Format button and then choose Clear Find.
You can use Excel's search muscles to find not only the information you're interested in, but also to modify cells quickly and easily. Excel lets you make two types of changes using its replace tool:
You can automatically change cell content . For example, you can replace the word Colour with Color or the number $400 with $40 .
You can automatically change cell formatting . For example, you can search for every cell that contains the word Price or the number $400 and change the fill color. Or, you can search for every cell that uses a specific font, and modify these cells so they use a new font.
Here's how to perform a replace operation. The box on Sidebar 5.4 gives some super-handy tricks you can do with this process.
Move to the cell where the search should begin.
Remember, if you don't want to search the entire spreadsheet, just select the range of cells you want to search.
Choose Edit Replace, or press Ctrl+H.
The Find and Replace window appears, with the Replace tab selected, as shown in Figure 5-12.
In the "Find what" box, enter your search term. In the "Replace with" box enter the replacement text.
Type the replacement text exactly as you want it to appear. If you want to set any advanced options, click the Options button (see the earlier sections Section 5.2.2 and Section 5.2.3 for more on your choices).
Perform the search.
You've got four different options here. Replace All immediately changes all the matches your search identifies. Replace changes only the first matched item (you can then click Replace again to move on to subsequent matches or to select any of the other three options). Find All works just like the same feature described on Sidebar 5.2. Find Next moves to the next match, where you can click Replace to apply your specified change, or click any of the other three buttons . The replace options are good if you're confident you want to make a change; the find options work well if you first want to see what changes you're about to make (although either option can be reversed using the normal Edit Undo command).
| POWER USERS' CLINIC |
Mastering the Art of Replacement
You can use the find and replace feature in many imaginative ways. Here are just a few examples: