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 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 Chapter 3 (Using Functions and Creating Formulas) for the use of regular expressions within formulas.