• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL

Chapter 9. Spreadsheet Essentials > Finding and Replacing Data

Finding and Replacing Data

Another way to edit a worksheet is to use the Replace command to search for a string and replace it with another.

To perform a Find/Replace

Optional: To restrict your search to a specific part of the worksheet, select that range.

Choose Edit > Replace.

The Replace dialog box appears (Figure 9.13).

Figure 9.13. Enter Find what and Replace with strings in the text boxes.

Enter a search string in the Find what text box and a replacement string in the Replace with text box.

From the Within drop-down menu, choose Worksheet to search only the active sheet or Workbook to search all sheets.

From the Search drop-down menu, choose By Rows or By Columns, depending on how the data is arranged.

For example, to replace an old company name or address with a new one in a worksheet that restricts those items to one or two columns, a By Columns search is best. If the text could be found in any column, on the other hand, a By Rows search might be better.

Optional: Click one or both check boxes to restrict matches to those with identical letter case (Match case) or where the Find what string is the only data in the found cell (Find entire cells only).

To begin the search, click Find Next.

If Excel finds a match, it highlights it in the worksheet (Figure 9.14).

Figure 9.14. When a match is found, Excel scrolls to it and selects the cell.

For each match, do one of the following:

  • To replace this match with the Replace with string, click Replace.

  • To skip this match and look for the next one, click Find Next.

  • To skip this match and end the search immediately, click Close.

Repeat this step to find and handle any additional matches in the worksheet or workbook.



Not a subscriber?

Start A Free Trial

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint