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 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.

1 Simple Find dialog
2 The Find tab
3 The Replace tab

Simple Find dialog

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

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 or , 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.

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 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 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, sd 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.

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 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 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.

Skip Navigation Links

All Books

Origin Help

Workbooks Worksheets and Worksheet Columns

Worksheets

English | Deutsch | 日本語

4.2.3 Finding and Replacing Worksheet Data

Contents

Simple Find dialog

The Find tab

The Replace tab