To replace a particular text string, value or result of formula with another one, click on the Replace button on the toolbar or select the Replace command from the Edit menu. The "Replace" dialog box is displayed.
This is very similar to the "Find" dialog box. However, it has some additional sections.
In the Find what box, type the text, value or result of a formula you wish to find, or paste it in from the clipboard.
In the Where box select Values if you only want to find and replace text or values; select Formulas if you only want to find and replace the result of a formula; select Both if you want to find and replace either text/values or the result of a formula. For instance, if you enter 53 in the Find what box, and if cell A10 contains the value 53, while cell A11 contains 53 as the result of a formula, say =21 + 33, then setting Values will find A10 but not A11, setting Formulas will find A11 but not A10, and setting Both will find both A10 and A11.
In the Replace with box, type the text, value or formula you want to replace the found item (or paste it in from the clipboard). Note that you can replace a value or text with a formula, or an existing formula with another, and vice versa.
In the Direction and Scope boxes, choose where you would like to look for and replace the text, value or result of a formula:
All |
Search from the beginning of the spreadsheet or selection |
Down |
Search from the active cell to the end of the spreadsheet or selection |
Up |
Search from active cell to the beginning of the spreadsheet or selection |
Whole document |
Search the whole document |
Selection |
Search only the selected cells. Use this option to search a specific row or column by first selecting a row or column, or to search a selected area |
Select the Match Case checkbox to find only those occurrences with the combination of uppercase and lowercase letters specified in the Find what box.
Select the Match whole words only checkbox to find occurrences that are words by themselves, and not part of a larger word.
When everything is set, click on the Find Next button and Ability will find the next occurrence of the text, value or result of formula as specified. If no matches are found, a message will tell you that the search was unsuccessful.
Since Find Next finds values one by one you can choose to replace these as you go along by clicking on the Replace button. Ability will then automatically replace the present occurrence and proceed to search for the next one. If you choose not to replace a particular occurrence, click on Find Next instead. Alternatively, click on the Replace All button to replace all occurrences of the find item with the replace item.
See also: