Using the Find and Replace Features
Since we have been looking at ways to enhance the look of your Calc spreadsheets, it makes sense to also take a look at some of the features that will assist you as you edit and fine-tune your spreadsheets. A very useful tool is the Find feature. It allows you to find a text or numeric string anywhere in your spreadsheet.
And if you wish to search for a particular item and then replace it, the Find feature has you covered. You can even search for items using a set of wildcard and placeholder characters that allows you to search for items by using a portion of a text or numerical string as the search string.
To start the Find feature, click the Find tool on the Main toolbar, or select Edit, then Find and Replace. The Find and Replace dialog box opens. Figure 4.16 shows the Find and Replace dialog box with the More button selected.
Figure 4.16. You can use the Find feature to search for items in a spreadsheet and then replace them.
To search for a particular item such as a value or text string, type the information in the Search for box. To search for the string you entered, click the Find button. The Find feature will search out the first occurrence of the item in the spreadsheet and then select it.
Note
When you are working with the Find and Replace features, you may have to move the Find and Replace dialog box (drag it out of the way) to view the results of a particular search.
Once the search is complete, you can close the Find and Replace dialog box using the Close button. Even after you close the dialog box, you can quickly repeat the last search that you did. Press Shift + Ctrl + F. The next occurrence of the search item will be selected.
You can also search for all the occurrences of the item that you are searching for. Just click the Find All button. All occurrences of the search string will be highlighted on the current sheet (you can also search for the item on all the sheets of a spreadsheet, which is discussed in the “To Use the Search Options” section that follows).
To Use Replace
You can also search for items in your spreadsheets and then replace them with another entry. To do a search and replace, enter a search string in the Search for box and then enter the replacement string in the Replace with box.
You can then conduct your search and replace by clicking the Replace button. The first occurrence of the search string (in relation to where the active cell was in the sheet before you invoked the Find and Replace dialog box) will be replaced by the Replace with string.
Note
When using search and replace, press Ctrl + Home to go to the beginning of the current sheet before you open the Find and Replace dialog box. This will start the search at the beginning of the sheet.
If you wish to replace all occurrences of the search string, click the Replace All button.
To Use the Search Options
A number of different search options are provided by the Find and Replace dialog box. These options are:
Entire Cells— This option searches for cells that exactly match your search criteria.
Backwards— This search begins at the active cell and moves toward the beginning of the sheet or spreadsheet.
Regular Expression— This option allows you to use placeholders (also known as wildcards) in your search criteria string. A number of different placeholders are available. Table 4.1 lists some of more useful placeholders available.
| Character | Use |
|---|---|
| . (period) | Can stand for any character as a universal wildcard. |
| * | Stands for unlimited or no characters. Placed before an item that is being searched for that may be preceded by any number of characters. |
| ^ | Placed before the search string. Only entries that begin with the search string will be found. |
| $ | Placed at the end of the search string. Used to find the search criteria when it appears at the end of a text string in a cell. |
| [ ] | Used to bracket characters that will be searched for. For example, [123] means that all the characters between the brackets must be found in a cell for it to match the search criteria. |
| < | The search criteria must appear at the beginning of a text string for a match. |
| > | The search criteria must appear at the end of a text string for a match. |
Match Case— The case of the search criteria must be matched for a positive search result.
Current Selection Only— This search will only occur in the area that you selected on the current sheet.
Search for Styles— Search for specific styles and then replace them with other styles. Styles are discussed in detail in Chapter 7.
Similarity Search— This option searches for entries that are similar to the search criteria. Once this checkbox is selected, the Settings button (…) becomes active. Click the button and use the Similarity Search dialog box to enter the number of characters that a matching item can vary from the actual search criteria. The Similarity Search dialog box allows you to specify the number of characters that the entries can vary (Exchange characters) and the number of additional or fewer characters that will be allowed for a match (Add characters and Remove characters, respectively).
Other search options are also available in the Search in, Search directions, and Extras areas of the Find and Replace dialog box. These additional options are:
Notes— Search for notes in the spreadsheet (notes are discussed in the “Tracking Changes” section of this chapter).
Option buttons are also provided that allow you to control the search direction. The rows option searches by row in a horizontal fashion. The Columns option searches by column in a vertical fashion.
Finally, if you have a multiple sheet spreadsheet, you can conduct the search on all sheets in the spreadsheet. Select the Search in All Sheets option in the Extras area of the Find and Replace dialog box.