Find and replace

In practice, spreadsheets are often quite large. So you need a quick way of locating information even in a single worksheet. In spreadsheet documents you can find text, formulas, and styles (we only mention styles here; working with styles is outside the scope of this tutorial). You can navigate from one result to the next, or you can highlight all matching cells at once, then apply another format or replace the cell contents by other content. Calc can find cells that contain text or numbers that were entered directly as in a text document or result from a calculation. The search method depends on the type of data you are searching for.

Find an item

One menu item Find & Replace satisfies both finding without replacement and finding with replacement. To just search for an item, leave the Replace window blank.

Follow Along
Go to the sheet that includes the collection of autofilled rows from the previous activities. Find & Replace dialog Select Edit > Find & Replace or use the key combination Ctrl+F. The Find & Replace dialog displays. Type '6' in the Search for window. Click Find All to highlight all occurrences of the item. All cells containing the digit '6' are highlighted. Result of Find All when search value is '6' Now try finding the '6's one at a time. Click Find to locate the first occurrence of a '6'. Click Find repeatedly to locate further occurrences. Note that you'll need to click Yes in the display box that indicates that Calc reached the end of the document, so you can continue searching from the beginning.

Tip: Although cell contents can be formatted in different ways (for example, a number can be formatted as currency, to be displayed with a currency symbol), the formatting symbols (in the example, the currency symbol in the cell) are not searchable.

Replace the item

The Find command simply locates occurrences of an item. It does not change these in any way. You may do so manually, if you wish. The Replace command on the other hand is useful when we wish to change entries. The Replace command provides us with two methods of replacement.

• Replace gives you the option of changing each occurrence or not.
• Replace All replaces all occurrences of the item with the new entry without further prompting.

Follow Along
Continuing with the worksheet used in the last activity. Let's say that you want to change instances of the number '1' to the word 'yes'. You think to yourself, "I want to change all of the instances of the number '1' to be 'yes' in this worksheet, so I can use Replace All." Let's try it out. If you closed the Find & Replace dialog following the last activity , select Edit > Find & Replace. (Note that the dialog box stays open until you choose Close.) Enter '1' in the Search for window and 'yes' in the Replace with window. Click Replace All. Calc replaces all uses of the digit '1' with the word 'yes' without any further prompting. Thankfully each edited cell is highlighted so you can easily see the error in your logic. Yikes, Replace All changed every instance of the digit 1 to 'yes' Click the Undo button, , to correct the mistake. As you can see, in many instances it is better to start with selective replacement, to ensure that no unwanted entries will be replaced. Let's change the last instance of 'Alpha' in row 8 to 'Iota' the next letter in the Greek alphabet. Enter 'Alpha' in the Search for window and 'Iota' in the Replace with window. Click Find. Did Calc find the A8 or I8 cell? If A8 is selected, click Find again. If I8 is selected, click Replace. Only the listing of 'Alpha' as the last letter in the series is changed to 'Iota'. Only the last instance is changed to 'Iota'

Use Replace All with caution; otherwise, you may end up with some highly embarrassing mistakes. A mistake with Replace All might require a manual, word-by-word search to fix, if it is not discovered in time to Undo.

Find and replace options

At the bottom left of the Find & Replace dialog, there are two options:

Match case: By default, Find ignores the case of the text. When Match case is checked, the Find command will distinguish between upper and lower case characters, identifying only text that matches the capitalisation used in the Search for window.

Entire cells: By default, the Find command looks anywhere in the cell for the search data. When Entire cells is checked, Find searches for whole words or cells that are identical to the search text. For example, searching in a list of names for the letters 'and' might find the following matches.

Searching for 'and' in a list of names

But when Entire Cells is checked, the same search would result in the message .

Notice the More Options button, , in the bottom left corner of the Find & Replace dialog. When this button is clicked, additonal options display.

Find & Replace dialog showing additional options

Let's discuss a few of them.

Current selection only: When checked, only the highlighted area in the worksheet is searched and/or revised. This option is helpful when data in different sections of the worksheet have different usage. For example, when gender is coded as 1 or 2 in column C, and numbers including the digits 1 and 2 are in other columns, you can safely use Replace All to change '1' to 'female' if the command is limited to column C only.

Backwards: This function reverses the normal direction that the Find procedure takes. By default. Find moves from left to right and from the top downwards in a worksheet. When you check the Backwards option, Find will then proceed from the bottom up and from right to left.

Tip: A search always begins where the cursor is located.

Regular expressions: When checked, Calc allows the use of wildcards in a search. (Regular expressions are very powerful but not very intuitive, and as such are beyond the scope of this tutorial. A list of regular expressions is provided in OpenOffice.org Help. A helpful discussion of wildcards is available in the OpenOffice.org Calc documentation, see the last section on the page.)

Search in: The drop-down box offers choices for where to search.

• Formulas finds parts of formulas.
• Values finds the results of calculations.

Search in all sheets: By default, Calc searches the current sheet. Check Search in all sheets to search through all sheets of the document.

|