4.2.3 Finding and Replacing Worksheet Data


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

To open the Simple Find dialog:

  • Select Edit: Find
  • Press Ctrl + F key
  • Click the Find button in the Mini toolbar for worksheet

Click the Find More.png button in Simple Find dialog to open the Find and Replace dialog with the Find tab active.


Select Edit: Replace in the menu to open the dialog with the Replace tab active directly.

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.
  • To find and replace numeric or string in cell formulas, you will need to show formula (select Show Formula mini toolbar button) or switch to Edit Mode (Edit: Edit Mode) first.



Simple Find dialog

Use this dialog search numeric or string data in a worksheet.

Simple Find dialog.png

  • Click the minimize button of the dialog or press Win+Down key to minimize dialog, and then you can select the range in the worksheet to limit the selected range.
  • Besides click the Find Previous Button.png or Find Next Button.png, you can press Ctrl + Page Up/Down key to search previous/next in the selected range.

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.

To replace a numeric with a string, select String and check the Match whole cell contents box.

OH replace numeric with string.png
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.