Find and replace is a powerful tool in Collabora’s Calc spreadsheets. Starting from the Home tab, you can access features such as wildcards, regular expressions, similarity search, formatted display, and many, many more. In this article, we will guide you through the many different ways you can use this feature to improve your data editing skills.
Finding Words 101: A Step-By-Step Tutorial
You can find cells containing, for example, “apple”, as shown in the image below:
To find only the word “apple” and nothing else, use the Entire Cell option.
You can also find all cells where “apple” appears at the end using the Wildcards option, under Other Options; if you enter an asterisk before the word “apple” itself, you’ll see all cells that end with “apple”.
In order to find cells that start with “apple” as a separate word, we use Regular Expressions – the regular expression for a space, and the caret character indicate the start of a cell. Click Find All, and you’ll see all cells starting with “apple”.
Finding Numbers
We can search for the number “1234” and all the cells containing that number will be highlighted.
We can also find cells where the number is formatted with a separator, such as “1,234”.
Additionally, if cells are formatted with separators, selecting Formatted Display will highlight these cells.
As a final example, we can search for cells containing a currency sign: you can see that all cells with a currency symbol are identified.
Searching within Formulas
You can distinguish between values and formulas using the Values option under Other Options.
For example, let’s search for the value “12”. We see two cells containing the number 12.
Now, let’s search for “12” within formulas.
The results include formulas that contain the number 12 directly, as well as the formulas where it’s a part of a combined text reference, such as in Cell B12.
You can also search within comments – let’s look for the word “apple”.
Replacing Cell Styles
We have a sheet with two simple styles: one with a yellow background and another with a green background; let’s say we want to replace the green style: we select it and replace it with a nice, blue “Sea” style.
Click Replace All to apply the changes.
Don’t miss out on any future tips and tricks – subscribe to our Youtube channel!
