Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Mail Systems
Eclipse Documentation

How To Guides
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Problem Solutions




OpenOffice Calc 3.x Guide
Previous Page Home Next Page

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).
Image:Tip.png 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 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.

OpenOffice Calc 3.x Guide
Previous Page Home Next Page

  Published under the terms of the Creative Commons License Design by Interspire