Calc Guide

3) Click Find, Find All, Replace, or Replace All (not recommended).
Tip
The online help describes many of the regular expressions and their
uses.
The following points may be of interest to Calc users:
• In Calc, regular expressions are applied separately to each cell. This means
that 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 the system will try
to match r followed by any other character followed by d.)
• When a match is found, the entire cell is highlighted but only the text found
will be replaced. For example, searching for brown will result in highlighting a
cell containing redbrown clay, and choosing nothing in the Replace with box
leaves the cell containing red clay.
• If Find is used twice in a row, and the second time the Current selection only
box is activated, then the second search will evaluate the whole of each
selected cell, not just the strings that 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), it may be found by
using the regular expression \n. For example, if a cell contains red [hard line
break] clay then searching for d\nc and entering nothing in the Replace with
box leaves the cell containing relay.
• The hard line break acts to mark “end of text” (similar to “end of paragraph” in
Writer), found by the regular expression special character $, in addition 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.
• 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 within formulas, values, or
notes. This option applies to any search, not just one using regular
expressions. Searching with the Formulas option for SUM would find a cell
containing the formula =SUM(A1:A6) as well as a cell containing the simple
text SUMMARY.
• Searching for the regular expression ^$ will not find empty cells. This is
intentional, 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.
• Finding cell contents using the regular expression .+ (or similar) and replacing
them with & effectively re-enters the cell contents without any formatting. This
technique can be used to remove formatting automatically applied by Calc
when importing data from the clipboard or from badly formatted files. For
example, to convert text strings consisting of digits into actual numbers, first
format the cells as numbers and then perform the search and replace.
See Chapter 7 (Using Formulas and Functions) for the use of regular expressions
within formulas.
68 OpenOffice.org 3.3 Calc Guide