CCNC/CCNC Module 4/Working with and in cells/Using the Search and Replace Command

From WikiEducator
Jump to: navigation, search
Tutorial.png Presentation Basics 

Selection in a worksheet | Inserting within a worksheet | Deleting rows and columns in a worksheet | Modifying column widths and row heights | Entering data in cells | Making changes to the contents of cells | Shifting cells | Using the Search and Replace Command | Sorting data | Show Me Xine.png | Summary & FAQs

Using the Search and Replace Command

In practical situations spreadsheets are often quite large. So you need a quick way of locating information even in a single worksheet. First we will find an item then we will replace it with something else. Note that thee Find command simply locates occurences of the item(s). It does not do anything to alter their state. The replace command allows you to remove an item and replace it with something else.

(Comment.gif: I am using the terms 'command', 'tool' and 'function' loosely throughout the module. Please check.)

Now do it

Find an item

  • Edit->Find and Replace
  • Type the Item in the Search For Window
  • Click Find All to highlight all occurences of the item, as shown in scrennshot below


  • Click Find to locate the first occurence of the item only.
  • Click Find repeatedly to locate further occurences.


Replace the item

  • Edit->Find and Replace
  • Type appropriate entries in the Search for window and the Replace with window
  • Notice that CALC replaces all existing entries found with the entry in the Replace With window.

We need to distinguish between global replacement and selective replacement. We do global replacement when we replace all occurences of a particular item with another specific item. For example, we can replace all examples of Andiswa with Andisa. In such a case we will click Replace All. With selective replacement, we systematically click Find to locate each example of the item, then having found it, decide whether or not to replace it. If yes, we Click Replace for that item only.

More on find and replace options

At the bottom left corner of the Find and Replace box there are other functions that may come in useful from time to time. Here is a brief explanation of three of them:

  • Entire Cells: This function overrides Find. By default, the Find procedure looks anywhere in the cell for the search data.


  • Backwards: This function reverses the normal direction that the Find procedure takes. By default. Find moves from left to right and from the top downwards in a worksheet. When you check the Backwards option, Find will then proceed from the bottom up and from right to left.
  • Match case: Typically, Find is case insensitive, meaning that it will ignore the case of the text. If you wish your search to locate items in which upper case and lower case are used in exactly the same way in the Search item and the text, you click this box. The Search is then said to be case sensitive.