OpenOffice/Calc 3/Working with and in cells/The search and replace command

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.

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.

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.

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

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.