4.2.3 Finding and Replacing Worksheet Data

Finding and replacing worksheet data can be done with the Find and Replace tool.

You can open it from the menu by selecting either Edit: Find or Edit: Replace. The former opens the dialog with the Find tab active; while the latter opens the dialog with the Replace tab active.

This tool works with both numeric and string data.

You can also use the wreplace X-Function to replace numeric or string characters in worksheets.

The Find tab

Use the Find tab to search numeric or string data in a worksheet.

Find and replace-1.png
Find and replace-2.png
Data Type

Specify the type of the data to be searched.

  • Numeric
  • String
Condition

This is only available when Data Type is Numeric. With it, you can specify the operator used to build the condition. For example, if you choose > with the Condition drop-down list and enter 3 in the Find What edit box, the tool will search for data values that are greater than 3.

  • =: Equal to
  • <: Less than
  • <=: Not greater than
  • >: Greater than
  • >= : Not less than
  • !=: Not equal to
Find what

Specify the value to be found.

When the Numeric radio button is selected in the Data Type group, click the Right Triangle button Button Select Data Right Triangle.png to the right of the Find What edit box, and then you can select one of the following from the short-cut menu:

  • -- Missing value only
    "--" will be dipslayed in the Find What edit box. It stands for missing values.
  • - Missing value/Blank Cell/Text Cell
    "-" will be dipslayed in the Find What edit box. It stands for missing value, blank cell or text cell.

When the String radio button is selected in the Data Type group and the Use wildcards check box is selected, click the Right Triangle button Button Select Data Right Triangle.png to the right of the Find What edit box, and then you can select one of the following from the short-cut menu:

  • * any string of characters
    * can be used to represent a string with an arbitrary length. For example, s*d finds "sad" and "started".
  • ? Any single character
    ? can be used in the Find What edit box as a substitute for any single character. For example, s?t finds "sat" and "set".
Options
Skip linked cells
Specify whether or not to skip cells that contain links to other worksheet cells or report sheets.
Find/Replace in selected range
If a range has been selected before the dialog is opened, this check box will become available in the dialog and will be selected by default. You can use it to specify whether or not to limit the find range to the selected range.
Use absolute value
This is only available when the Numeric radio button is selected in the Data Type group. Use it to specify whether to use the absolute value of the cell value to test the given condition.
Search up
Specify whether to search upward.
Tolerance
This is only available when the Numeric radio button is selected in the Data Type group. Use it to specify the tolerance between the worksheet data value and the Find What value. Origin will treat the two numbers, a and b, as equal if \frac{\mid a-b\mid }{\mid a\mid +\mid b\mid }<Tolerance
Match case
This is only available when the String radio button is selected in the Data Type group. Specify whether or not the search is case-sensitive.
Match whole cell contents
This is only available when the String radio button is selected in the Data Type group. And it is enabled only when the Use wildcards check box is unchecked in the Options group. Use it to specify whether or not to compare the Find What value with the whole cell content. When this check box is selected, only when the Find What string is a match with the entire cell will the tool consider the Find What string is found.
Use wildcards
This is only available when the String radio button is selected in the Data Type group. Use this to specify whether or not the wildcards, ? and *, are used in the Find What edit box. If this check box is selected, the Match whole cell contents check box will be disabled.
Include label rows
This is only available when the String radio button is selected in the Data Type group. Use this to specify whether or not to include the column label rows in the search range.
Find Next

Find the next occurrence of the value that meets the search condition.

Close

Close the Find and Replace dialog box.

The Replace tab

Use the Replace tab to replace a numeric or string value with another value.

Find and replace-3.png
Find and replace-4.png
Data Type

Specify the type of the data to be replaced.

  • Numeric
  • String
Condition

This is only available when Data Type is Numeric. With it, you can specify the operator used to build the condition. For example, if you choose > with the Condition drop-down list and enter 3 in the Find What edit box, the tool will search for data values that are greater than 3.

  • =: Equal to
  • <: Less than
  • <=: Not greater than
  • >: Greater than
  • >= : Not less than
  • !=: Not equal to
Find what

Specify the value to be replaced.

When the Numeric radio button is selected in the Data Type group, click the Right Triangle button Button Select Data Right Triangle.png to the right of the Find What edit box, and then you can select one of the following from the short-cut menu:

  • -- Missing value only
    "--" will be dipslayed in the Find What edit box. It stands for missing values.
  • - Missing value/Blank Cell/Text Cell
    "-" will be dipslayed in the Find What edit box. It stands for missing value, blank cell or text cell.

When the String radio button is selected in the Data Type group and the Use wildcards check box is selected, click the Right Triangle button Button Select Data Right Triangle.png to the right of the Find What edit box, and then you can select one of the following from the short-cut menu:

  • * any string of characters
    Use * in the Find What edit box as a substitute for zero or more characters.
  • ? Any single character
    Use ? in the Find What edit box as a substitute for a single character.
Replace with

Specify the new value used to replace the found values.

Look in

Specify where to look for the value to be replaced. It is available only when Find/Replace in selected range is unchecked in the Options group.

  • Active Worksheet
    The active worksheet.
  • Active Workbook
    The sheets in the active workbook.
  • All Workbooks in Active Folder
    All workbooks in the active folder, excluding its subfolders.
  • All Workbooks in Active Folder (Recursive)
    All workbooks in the active folder, including its subfolders.
  • All Workbooks in Active Folder (Open)
    All non-hidden workbooks in the active folder
  • All Workbooks in Project
    All workbooks in the current project.
Options
Skip linked cells
Specify whether or not to skip cells that contain links to other worksheet cells or report sheets.
Find/Replace in selected range
If a range has been selected before the dialog is opened, this check box will become available in the dialog and will be selected by default. You can use it to specify whether or not to limit the find range to the selected range.
Use absolute value
This is only available when the Numeric radio button is selected in the Data Type group. Use it to specify whether to use the absolute value of the cell value to test the given condition.
Keep sign if condition true
This is only available when the Numeric radio button is selected in the Data Type group. Use it to specify whether to keep the original sign of the found value when the condition is met and the found value should be replaced.
Set as missing value if condition false
This is only available when the Numeric radio button is selected in the Data Type group. If this check box is selected, values that do not meet the condition will be set as missing.
Search up
Specify whether to search upward.
Tolerance
This is only available when the Numeric radio button is selected in the Data Type group. Use it to specify the tolerance between the worksheet data value and the Find What value. Origin will treat the two numbers, a and b, as equal if \frac{\mid a-b\mid }{\mid a\mid +\mid b\mid }<Tolerance
Match case
This is only available when the String radio button is selected in the Data Type group. Specify whether or not the search is case-sensitive.
Match whole cell contents
This is only available when the String radio button is selected in the Data Type group. And it is enabled only when the Use wildcards check box is unchecked in the Options group. Use it to specify whether or not to compare the Find What value with the whole cell content. When this check box is selected, only when the Find What string is a match with the entire cell will the tool consider the Find What string is found.
Use wildcards
This is only available when the String radio button is selected in the Data Type group. Use this to specify whether or not the wildcards, ? and *, are used in the Find What edit box. If this check box is selected, the Match whole cell contents check box will be disabled.
Include label rows
This is only available when the String radio button is selected in the Data Type group. Use this to specify whether or not to include the column label rows in the search range.
Find Next

Find the next occurrence of the Find What value.

Replace

Replace the current found value.

Replace All

Replace all occurrences of the Find What value.

Close

Close the Find and Replace dialog box.