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

Data Type Specify the type of the data to be searched. Numeric String 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 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, 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". 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 } 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 the next occurrence of the value that meets the search condition. Close the Find and Replace dialog box.

## The Replace tab

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

Data Type Specify the type of the data to be replaced. Numeric String 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 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. Specify the new value used to replace the found values. 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. 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 } 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 the next occurrence of the Find What value. Replace the current found value. Replace all occurrences of the Find What value. Close the Find and Replace dialog box.