2.2.3.63 wreplace

1 Brief Information
2 Command Line Usage
3 X-Function Execution Options
4 Variables
5 Description
6 Examples
7 Related X-Functions

Brief Information

Find and replace cell value in a worksheet

Command Line Usage

1. wreplace find_value:=0 replace_value:=0 cond_value:=lt;
2. wreplace rng:=col(1) type:=str find_str:="a*c" replace_str:="abc" wildcards:=1;
3. wreplace col(A)[1]:end[40] find_value:=-- replace_value:=0;

X-Function Execution Options

Please refer to the page for additional option switches when accessing the x-function from script

Variables

Display Name	Variable Name	I/O and Type	Default Value	Description
Input	rng	Input/Output Range	<active>	Specify the range to perform the replacing.
Data Type	type	Input int	0	Specify the type of data to find and replace. Option list: num:Numeric To find and replace numeric data. str:String To find and replace string.
Find What (-- for Missing Value)	find_value	Input double		This is only available when Number (num) is selected for Data Type (the type variable). Combined with the operator selected for the Condition variable, the value of this variable defines the condition used to find the values to be replaced. "--" stands for the missing value here.
Find What	find_str	Input string		This is only available when String (str) is selected for Data Type (the type variable). Use it to specify the string to find.
Condition	cond_value	Input int	0	This is only available when Number (num) is selected for Data Type (the type variable). It specifies an operator. Combined with the value specified by Find What (the find_value variable), we can define the condition that is used to find the data to be replaced. Option list: eq:Data= Find data that is equal to the Find What value. lt:Data< Find data that is less than the Find What value. le:Data<= Find data that is not greater than the Find What value. gt:Data> Find data that is greater than the Find What value. ge:Data>= Find data that is not less then the Find What value. ne:Data<> Find data that is not equal to the Find What value.
Replace With	replace_value	Input double		This is only available when Number (num) is selected for Data Type (the type variable). It specifies the new value with which all the found cells will be replaced.
Replace With	replace_str	Input string		This is only available when String (str) is selected for Data Type (the type variable). It specifies the new string with which all the found strings will be replaced.
Tolerance	tolerance	Input double	1E-8	This is only available when Number (num) is selected for Data Type (the type variable). It specifies the tolerance used to find the worksheet data to be replaced. If the absolute difference between the worksheet data value and the Find What value is less than the tolerance, these two values will be regarded as equal.
Use Absolute Value	use_abs	Input int	0	This is only available when Number (num) is selected for Data Type (the type variable). It specifies whether or not to use the absolute values of the worksheet data to test the given condition. This is disabled when Condition is ">" or "<" or "<>".
Keep Sign if Condition True	keep_sign	Input int	0	This is only available when Number (num) is selected for Data Type (the type variable). It specifies whether to keep the sign of the original value if the condition is true and the worksheet data is replaced.
Set as Missing if Condition False	set_missing	Input int	0	This is only available when Number (num) is selected for Data Type (the type variable). It specifies whether or not to set missing value to cells whose original values do not meet the condition.
Match Whole Word Only	cond_str	Input int	0	This is only available when String (str) is selected for Data Type (the type variable). It specifies whether the string to find should match the exact whole string you typed.
Case Sensitive	con_case	Input int	0	This is only available when String (str) is selected for Data Type (the type variable). It specifies whether the string search is case sensitive or not.
Skip Link Cells	skip_link	Input int	0	Specify whether to skip cells that contain links.
Use Wildcards	wildcards	Input int	0	Specify whether wildcards are allowed or not. If wildcards are used, "*" can stand for arbitrary string and "?" can stand for any arbitrary character.
Include Label Rows	label	Input int	0	Specify whether to include column label rows (LongName, Unit, Comment, etc.) to search.
	undo	Input int	0	Specify whether to allow undo.
Look In	lookin	Input int	6	Specify where to search the specified value or string. Option list: active_sheet:Active Worksheet The active worksheet will be searched. active_book:Active Workbook The active workbook will be searched. all_in_active_folder:All Workbooks in Active Folder All workbooks in the active folder will be searched. But those in the subfolders will not be searched. recursive_in_active_folder:All Workbooks in Active Folder (Resursive) All workbooks in the active folder will be searched, including those in the subfolders. open_in_active_folder:All Workbooks in Active Folder (Open) All non-hidden workbooks in the active folder will be searched. But those in the subfolders will not be searched. all_in_project:All Workbooks in Project All workbooks in the project will be searched. specified:Specified Range The range specified by the rng variable will be searched.

Description

This X-Function is capable of finding and replacing texts or numbers in worksheet cells.

Examples

Code Sample

/*
This example uses the X-Function wreplace to find the negative values, and replace them with zero.
1. Import the sample data into a book in Origin;
2. Find and replace those negative values in the 
active sheet with zero.
*/
// Create a new workbook
newbook;
// Import a file
string fname$ = system.path.program$ + "Samples\Curve Fitting\Multiple Peaks.dat";
impasc;
// Find and replace those negative values
wreplace type:=num find_value:=0 cond_value:=lt replace_value:=0 lookin:=active_sheet;
/*
The following example will use the X-Function wreplace to 
replace the missing values with 1250.
1. Import the sample data into a book in Origin.
2. Replace missing values with 1250.
*/
// Create a new workbook
newbook;
// Import a file
string fname$ = system.path.program$ + "Samples\Graphing\Box Chart.dat";
impasc;

// Replace the missing values with 1250
wreplace rng:=col(a)[1]:end[31] find_value:=-- replace_value:=1250;