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

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 worksheet(s).

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, the following wildcards can be used for searching: * 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". *~ followed by ?,, or ~** "~?" to find question marks, "~" to find asterisks, and "~~" to find other tilde characters. Note that when wildcards "?" or "" is used in the Find What string, the search must match the whole cell contents (no matter Match whole cell contents is checked or not).
Look in	Specify where to look for the Find What value. 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	Find/Replace in selected range If a range has been selected before the dialog is opened, this check box will become available and will be selected by default. 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$ Search Only Inside Cell Formulas This is only available when the String radio button is selected in the Data Type group. Use it to search in cells with cell formulas only. Note that it is NO need to show formula (select Show Formula mini toolbar button) or switch to Edit Mode (Edit: Edit Mode) first. If you want to include cell links in searching, you should uncheck this checkbox. Include Notes This is only available when the String radio button is selected in the Data Type group and Search Only Inside Cell Formulas is unchecked. Specify whether to include embadded Notes into worksheet cells while searching. Note that Notes of cell comment is not included in this case. 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. 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 considers the Find What string is found. Include label rows This is only available when the String radio button is selected in the Data Type group. 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.

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, the following wildcards can be used for searching: * 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". *~ followed by ?,, or ~** "~?" to find question marks, "~" to find asterisks, and "~~" to find other tilde characters. Note that when wildcards "?" or "" is used in the Find What string, the search must match the whole cell contents (no matter Match whole cell contents is checked or not).
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	Find/Replace in selected range If a range has been selected before the dialog is opened, this check box will become available and will be selected by default. 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. 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$ Search Only Inside Cell Formulas This is only available when the String radio button is selected in the Data Type group. Use it to search inside cell formulas only. Note that it is NO need to show formula (select Show Formula mini toolbar button) or switch to Edit Mode (Edit: Edit Mode) first. If you want to include cell links in searching, you should uncheck this checkbox. 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. 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.

4.2.3 Finding and Replacing Worksheet Data

Contents

Simple Find dialog

The Find tab

The Replace tab