Find and replace cell value in a worksheet
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;
Please refer to the page for additional option switches when accessing the x-function from script
| Display Name |
Variable Name |
I/O and Type |
Default Value |
Description |
|---|---|---|---|---|
| Input | rng |
Input/Output Range |
|
Specify the range to perform the replacing. |
| Data Type | type |
Input int |
|
Specify the type of data to find and replace.
Option list:
|
| 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 |
|
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:
|
| 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 |
|
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 |
|
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 |
|
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 |
|
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 |
|
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 |
|
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 |
|
Specify whether to skip cells that contain links. |
| Use Wildcards | wildcards |
Input int |
|
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 |
|
Specify whether to include column label rows (LongName, Unit, Comment, etc.) to search. |
| undo |
Input int |
|
Specify whether to allow undo. | |
| Look In | lookin |
Input int |
|
Specify where to search the specified value or string.
Option list:
|
This X-Function is capable of finding and replacing texts or numbers in worksheet cells.
/* 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;