Find and Replace Cell Values in Data Tables

You can find and replace cell values by selecting the Edit > Search > Find options.

Figure 4.20 The Find Window 

The following rules apply to searching for values:

• To find values in hidden columns, unhide the column.

• Values found in locked columns cannot be modified.

• The Undo command works only with Replace. You cannot undo Replace All.

• If your data table contains value labels, using the Search commands searches for actual values, but does not search for labels. See Value Labels.

• If your data table contains formatted values (such as dates, times, or durations) using the Search command searches for the formatted values, not the actual values.

Find Window Options

Refine your search with the following options:

Match Case

Performs a case-sensitive search, which can be useful for locating proper nouns or other capitalized words.

Match entire cell value

Detects empty spaces, which lets you search for a series of words in a character column, or locate strings with unwanted leading or trailing empty spaces.

Tip: To find missing character values, leave the Find what box empty and check Match entire cell value. To find missing numeric values, insert a period into the Find box and check Match entire cell value.

Use regular expressions

Assumes the find string to be a regular expression instead of the literal string that you enter in the Find what box. The regular expressions follow standard semantics.

Restrict to selected rows

Restricts the search to selected rows.

Restrict to selected columns

Restricts the search to selected columns.

Search data

Searches only data cells (omitting column names).

Search column names

Searches only column names (omitting data cells).

By column

Searches the table column by column, from top to bottom, until it reaches the last cell in the rightmost column, or until you stop the search.

By row

Searches the data table row by row from left to right, to the rightmost cell in the last row or until you stop the search.

Multiple lines

Increases the Find and Replace boxes to 3 lines long instead of 1. The Enter key inserts a return into the field.

Tip: When the Multiple lines setting is clicked, you can alternatively click and drag on the Find and Replace boxes to make them larger.

Keep dialog open

Keeps the Find window open during your search.

Search Actions

This section describes some common searches that you might perform.

Begin by searching for a value in the data table. The search begins with the first cell in the first column and searches every cell until it locates the value or reaches the end of the table.

To replace the currently highlighted cell value

Enter a value in the Replace with box and click Replace. Or, if the Search window is closed, select Edit > Search > Replace. If the replace value is a missing value, the currently highlighted cell content becomes a missing value.

To replace all occurrences of the specified value

Enter a value in the Replace with box and click Replace All. Or, if the Search window is closed, select Edit > Search > Replace All.

To replace the value and search for the next value

Enter a value in the Replace with box and click Replace. Or, if the Search window is closed, select Edit > Search > Replace and Find Next. Or, press Ctrl+L.

To use a selected value as the Find what value

In the data table, select a value. Select Edit > Search > Use Selection for Find. Next, select Edit > Search > Find. The value that you selected in the data table is already entered in the Find what field.

To use a selected value as the Replace with value

In the data table, select a value. Select Edit > Search > Use Selection for Replace to populate the Replace with field.

To find the next value when the Search window is closed

Select Edit > Search > Find Next. Or, press Ctrl+G, or F3, on Windows.

To find a missing value:

• To find missing character values, leave the Find what field empty and select Match entire cell value.

• To find missing numeric values, type a period into the Find what text box.