Replacing data pg 142 143

Replacing Data

We can use searching ability of Excel to search for a particular, number, word, part of word, phrases, etc and replace it with another.

1. Use menu Edit/Replace. The following dialog box will appear:

Replace						?X

Find what:
som							Find Next
							Close
Replace with:
sam

Search:	By Rows
							Match case
							Replace

Find entire cells only
Replace All

2. In a box under the Find What label, enter the word or number that you want to find.

3. In a box under the Replace with label, enter the word or number that you want as replacement.

Remember, in the Replace dialog box, you can also ask Excel to search By Rows (row to row) or By Columns (column to column). Use the combo box on the right of label Search.

You can set the search to look for matching case by clicking in the box on the left of the Match case label. You can also set the search to look for cells containing only the search string you indicated (not other item) by clicking on a box on the left of Find entire cell only.

4. Click Find Next to start the search. If Excel does not find the item that you asked for, it will display a message to that effect. If Excel does find the item that you asked for, it will stop and highlight the cell which contains the item. Click Replace to make the replacement. Automatically, Excel will continue looking for the next occurrence of the word.

142

TIP

Always check that Excel correctly highlights the word that you intended to replace before you click the Replace button. For example, you may want to replace 'time' with 'tame', but Excel will also find the word 'sometime' (unless you mark Find entire cells only). In such a case, if you click to replace 'time' with 'tame', 'sometime' will become 'sometame',

If you select Replace all, Excel will search and replace at one go all occurrences of the word, without highlighting the item one by one. However, if you are not careful, you may replace 'sometime' with 'sometame' when you really want to replace 'time' with 'tame'.

5. Click Close or press the Esc key to close the dialog box.

If you think that you want to reverse the action, use menu Edit/Undo before saving the file. After you save the file, you will not be able to use the Undo.

The Replace function can also be accessed from menu Edit/Find. After you click this menu, the Find dialog box will appear. In the dialog, you can click the Replace button to open the same Replace dialog box shown above.