Calc Guide

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).
Tip
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.
70 OpenOffice.org 3.x Calc Guide