2.2.3.63 wreplace


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;

Related X-Functions

mreplace