Section 5.2. Find and Replace

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.

5.2.1. The Basic Find

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.

Note: To assist frequent searches, Excel lets you keep the Find and Replace window hanging around (rather than forcing you to use it or close it, as is the case with many other dialog boxes). This means you can continue to move from cell to cell and edit your worksheet data even while the Find and Replace window remains visible.
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!

5.2.2. More Advanced Searches

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.

Figure 5-9. Clicking the Options button in the standard Find and Replace window (top) gives you a slew of additional settings (bottom) that allow you to configure things like search direction, case sensitivity, and format matching.

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).

Note: The search direction determines which path Excel follows when it's searching. However, the search will still ultimately traverse every cell in your worksheet (or the current selection).
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.

Note: Remember, Excel searches for numbers as they're displayed (as opposed to looking at the underlying values that Excel uses to store numbers internally). That means that if you're searching for a number formatted using the dollar Currency format ($32.00, for example), and you've turned on the "Match entire cell contents" checkbox, you'll need to enter the number exactly as it appears on the worksheet. Thus, $32.00 would work, but 32 alone won't help you.

POWER USERS' CLINIC
Using Wildcards

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.

5.2.3. Finding Formatted Cells

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.

Figure 5-10. In the Find Format dialog box, Excel won't use any formatting option that's blank or grayed as part of it's search criteria. For example, here, Excel won't search based on alignment. Checkboxes are a little more tricky. In some versions of Windows, it looks like the checkbox is filled with a solid square. In other versions of Windows, it looks like the checkbox is dimmed and checked at the same time (as with the "Merge cells" setting in this example). Either way, this indicates that Excel won't use the setting as part of its search.

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.

Figure 5-11. The Find Format dialog box shows a basic preview of your formatting choices. In this example, the search will find cells containing the word "price" that also use white lettering, a black background, and the Bauhaus font.

{% if main.adsdop %}{% include 'adsenceinline.tpl' %}{% endif %}

To remove these formatting restrictions, click the pop-up menu to the right of the Format button and then choose Clear Find.

Note: Rather than specifying all the format settings manually, you can copy them from another cell. Just click the Choose Format From Cell button at the bottom of the Find Format dialog box. The pointer will change to a plus symbol with an eye dropper next to it. Next, click any cell that has the formatting you want to match. Keep in mind that when you use this approach, you copy all the format settings.

5.2.4. Finding and Replacing Values

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.

Figure 5-12. The Replace tab looks pretty similar to the Find tab. Even the advanced options are the same. The only difference is that you also need to specify the text you want to use as a replacement for the search terms you find.

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).

Tip: It's possible that a single cell might contain more than one match, in which case you'll need to replace each instance separately. Unfortunately, it's not clear in the Find and Replace window which instance you're replacing. You'll need to look through the whole cell.

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:

You can automatically delete a specific piece of text. Just enter the appropriate "Find what" text, and leave the "Replace with" box blank.
You can change the formatting used in specific cells. Just type the same text in both the "Find what" and "Replace with" text, and then click the Format button next to the "Replace with" combo box to set some formatting attributes. (You don't need to specify any formatting settings for your "Find what" search criteria.)
You can change the formatting used in a series of cells. For example, imagine you have a worksheet that has several cells bolded. Say you want to adjust the formatting of these cells to use a new font. To perform this operation, leave both the "Find what" and "Replace with" boxes blank. Then, set the formatting search criteria to look for the bold font attribute, and set the replacement formatting to use the new font. Click Replace All, and all the cells that currently have bold formatting will acquire the new font. This is tricky to master, but it's one of the most powerful formatting tricks around.