48. Find and Replace Data
BEFORE YOU BEGIN
41 Open an Existing Spreadsheet
43 About Moving Around Calc
53 Enter Calc Functions
You'll find yourself working with small, single worksheets quite a bit in Calc because each sheet usually represents one aspect of a financial analysis, such as weekly sales figures for a division. Nevertheless, you'll also work with large spreadsheets quite often too. Many times, a company needs to consolidate numbers from several different regions , companies, or departments into a single spreadsheet. Therefore, you'll combine numerous smaller sheets into a spreadsheet and consolidate them, report totals from them, and analyze them against one another.
Whether you have one large sheet or multiple smaller ones, being able to locate numbers and text easily is important. Calc provides powerful find and replace tools you can use to locate and change the data you want.
Unlike Writer's find and replace tools, when you search for Calc data, Calc highlights the entire cell that contains the data. So, if you were to search for the number 1 in a large spreadsheet, Calc locates and highlights the first cell (searching from left to right, row to row) that contains a 1 , even if other numbers and text are in that cell.
48. Find and Replace Data
Select the Find & Replace option from the Edit menu to display the Find & Replace dialog box. You can also click the Find button on the Main toolbar or press Ctrl+F to display the Find & Replace dialog box.
| | Enter Search Data
Type the data you want to find in the Search for
If you've searched for the same data before, you can click the down arrow to open the Search for drop-down list box and select the data to search for it once again.
Start the Search
Click the Find button. Calc searches from the current cell cursor's position in the sheet to the end of the sheet. If Calc finds the data anywhere within a cell, Calc highlights that cell. (If you click the Find All button instead of Find , Calc will highlight every cell that contains the matched data.)
Type Replacement Data
If you want Calc to replace found data with new data, type the new data into the Replace with text box.
Replace the Data
Click Replace . If the Search for data is found, Calc replaces that data with the data you entered in the Replace with text box.
Replace All Occurrences
Instead of Replace (or after you perform one or more replacements ), if you click the Replace All button, Calc replaces all the matches with your replacement data throughout the sheet. Such a change is more global and possibly riskier because you may replace data you didn't really want replaced . By clicking Find before each replace operation, you can be sure that the proper data in the correct cell is being replaced, but such a single-occurrence find and replacement takes a lot of time in a long spreadsheet.
Control the Search
To control the way Calc searches the current sheet, or to enable Calc to search all sheets within the current spreadsheet, click the More Options button. Calc expands the Find & Replace dialog box with additional options.
Replace All Previous Matches
Click to select the Backwards option before doing a find or replacement if you want to find or replace from the current cursor's position back to the start of the sheet.
| | Specify How to Search
Perhaps you only want Calc to search formulas for calculated results that match your search term . If so, select Formulas
from the Search in
list. If you want Calc to search only in values and text you've typed, but not formulas, click to select Values
. (The Notes
option is available if you want to search for text inside notes you've attached to cells within the sheet.)
Selecting Formulas enables Calc to locate your search term in either formulas or results. Selecting Values only returns a match if your search term is found in an actual number or text and not if it's the result of a formula.
Indicate a Direction
Although Calc normally searches from left to right and from the top row down, you can specify that Calc search completely down the first column of data before moving to the next column. Click to select the Columns option if you want to search down entire columns before searching the next one.
Determine the Search Depth
If you want Calc to search throughout all sheets inside the current spreadsheet, click to select the Search in all sheets option. Unless you check this option, Calc only searches the currently active sheet (the one displayed) within the current spreadsheet.
When you finish finding and replacing all the data for this search session, click the Find & Replace dialog box's Close button to close the dialog box and return to the sheet's work area.