Finding and replacing in Calc

OpenOffice.org 3 Calc Guide

Chapter 2 Entering, Editing, and Formatting Data

< Previous Section

Next Section >

• Entering data using the keyboard
• Speeding up data entry
• Validating cell contents
• Editing data
• Formatting data
• Autoformat, themes, conditional formatting
• Hiding and showing data
• Sorting records
• Finding and replacing in Calc

< Previous Page

Next Page >

In spreadsheet documents you can find text, formulas, and styles. You can navigate from one result to the next using Find, or you can highlight all matching cells at once using Find All, then apply another format or replace the cell contents by other content.

Text or numbers in cells may have been entered directly or may be the result of a calculation. The search method depends on the type of data you are searching for.

By default, Calc searches the current sheet. To search through all sheets of the document, click More Options, then select Search in all sheets option.

Cell contents can be formatted in different ways. For example, a number can be formatted as a currency, to be displayed with a currency symbol. You see the currency symbol in the cell, but you cannot search for it.

Expanded Find & Replace dialog

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

Finding and replacing formulas or values

You can specify in the Find & Replace dialog to search in formulas or in the displayed values that result from a calculation.

1. To open the Find & Replace dialog, use the keyboard shortcut Control+F or select Edit > Find & Replace.
2. Click More Options to expand the dialog.
3. Select Formulas or Values in the Search in drop-down list.
   • Formulas finds parts of the formulas.
   • Values finds the results of the calculations.
4. Type the text you want to find in the Search for box.
5. To replace the text with different text, type the new text in the Replace with box.
6. When you have set up your search, click Find. To replace text, click Replace instead.

Finding and replacing text

1. Open the Find & Replace dialog, click More Options to expand the dialog, and select Values or Notes in the Search in drop-down list.
2. Type the text you want to find in the Search for box.
3. To replace the text with different text, type the new text in the Replace with box.
4. Click Find, Find All, Replace, or Replace All.

When you click Find, Calc selects the next cell that contains your text. You can edit the text, then click Find again to advance to the next found cell. If you closed the dialog, you can press Ctrl+Shift+F to find the next cell without opening the dialog.

When you click Find All, Calc selects all cells that contain your entry. Now you can, for example, apply a cell style to all at once.

Finding and replacing cell styles

To quickly change all the paragraphs of one (unwanted) style to another (preferred) style:

1. On the expanded Find & Replace dialog, select Search for Styles. The Search for and Replace with boxes now contain a list of styles.
2. Select the styles you want to search for and replace.
3. Click Find, Find All, Replace, or Replace All.

Using wildcards (regular expressions)

Wildcards (also known as regular expressions) are combinations of characters that instruct OOo how to search for something. Regular expressions are very powerful but not very intuitive. They can save time and effort by combining multiple finds into one.

To use wildcards and regular expressions when searching and replacing:

1. On the Find & Replace dialog, click More Options to see more choices. On this expanded dialog, select the Regular expressions option.
2. Type the search text, including the wildcards, in the Search for box and the replacement text (if any) in the Replace with box.
3. Click Find, Find All, Replace, or Replace All (not recommended).

The online help describes many of the regular expressions and their uses.

The following points are interesting to Calc users:

• In Calc, regular expressions are applied separately to each cell. So a search for r.d will match red in cell A1 but will not match r in cell A2 and d (or ed) in cell A3. (The regular expression r.d means “try to match r followed by any other character followed by d.”)
• When a match is found, the whole cell is shown highlighted, but only the text found will be replaced. For example, searching for brown will highlight a cell containing redbrown clay, and replacing with nothing will leave the cell containing red clay.
• If Find is used twice in a row, the second time with Current selection only' activated, then the second search will evaluate the whole of each selected cell, not just the strings found which caused the cells to be selected in the first search. For example, searching for joh?n, then activating Current selection only and searching for sm.th will find cells containing Jon Smith and Smythers, Johnathon.
• If a cell contains a hard line break (entered by Ctrl+Enter), this may be found by \n. For example if a cell contains red hard_line_break clay then searching for d\nc and replacing with nothing leaves the cell containing relay.
• The hard line break acts to mark “end of text” as understood by the regular expression special character $ (in addition of course to the end of text in the cell). For example, if a cell contains red hard_line_break clay then a search for d$ replacing with al leaves the cell with real hard_line_break clay. Note that with this syntax the hard line break is not replaced—it simply marks the end of text.
• Using \n in the Replace with box will replace with the literal characters \n, not a hard line break.
• The Find & Replace dialog has an option to search Formulas, Values, or Notes. This applies to any search, not just one using regular expressions. Searching with the Formulas option would find SUM in a cell containing the formula =SUM(A1:A6). If a cell contains text instead of a function, the text will still be found - so that the simple text SUMMARY in a cell would also give a match to SUM using the Formulas option.
• Searching for the regular expression ^$ will not find empty cells. This is intentional—the rationale being to avoid performance issues when selecting a huge number of cells. Note that empty cells will not be found even if you are only searching a selection.
• Find .+ (or similar) and Replace with & effectively re-enters the contents of cells. This can be used to strip formatting automatically applied by Calc (often needed to clean data imported from the clipboard or badly formatted files), for example, to convert text strings consisting of digits into actual numbers (the cells must first be correctly formatted numbers). The leading apostrophes, telling Calc to treat the numbers as text, are removed.

See Using Formulas and Functions for the use of regular expressions within formulas.