wreplace

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

Menu Information

Replace

Brief Information

Find and replace cell value in a worksheet

Command Line Usage


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

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

Example 1

This example is used to replace negative values with 0 in the active worksheet.

Create a new project.
Import the file <Origin Program Directory>\Samples\Curve Fitting\Multiple Peaks.dat.
Enter the script wreplace -d; in the command window to bring up the wreplace dialog box.
Set Data Type to Numeric and set Find What and Replace With both to zero. Change the Condition to be Data< and Look In to be Active Worksheet. Keep other settings unchanged.
Click the OK button, then all the negative values will be replaced with 0.

Example 2

Suppose you want to replace the string "Joe" in your worksheet with another string "Joseph":

Make the worksheet active.
Enter the script wreplace -d; in the command window to bring up the wreplace dialog box.
Select String from the Data Type drop-down list.
In the Find What edit box, type Joe.
In the Replace With edit box, type Joseph.
Click OK.

Related X-Functions

mreplace