2.3.2.2.2 %( ) Substitution - String Substitution

string-substitution

String Expression SubstitutionString Expression Substitution

%(str$)

Pass String to Command
LabTalk command accepts both numeric argument and string arguments. Numeric variable or expression can be passed directly e.g. worksheet -a 2*2 will add 4 new columns. While string argument must be surrounded by %( ) to pass the real-time resolved string to the command. E.g.

string name$ = "Book1";
//Make some other window in Origin active, then run the following to activate Book1
win -a name$; //not work since it's trying to active a window named "bookname$"
win -a %(name$) //works

Concatenate Strings within " "
Use %(str$) substitution when concatenating multiple strings with in " ".

Suppose current window is Book1 with Sheet1 active

string name$ = "wks.name$ in page.name$"; 
name$=; //dumps exactly "wks.name$ in page.name$"
string name$ = "%(wks.name$) in %(page.name$)";
name$=; //dumps Sheet1 in Book1

//no need to add %( ) if it's not in " "
string name$ =wks.name$ + " in " +page.name$; 
name$=; //dumps Sheet1 in Book1

Convert String to Number
For string that contains number only, use %(str$) to convert it to number. This is the reverse of $( ) which converts number to string

// Convert the string "456" to number 456
string myString$ = "456";
int myNum=%(myString$);

Range Expression Substitution

%(rangeVariable)
When the %( ) substitution is used on a range variable, it always resolves it to a rangestring.

range rA=[Book1]Sheet1!A;
type %(rA); //output the rangestring

Because of this, ser can construct new range based on existing range variable, which helps to centralize code.

range rwks = sheet1!; //rwks points to sheet1 of current book
range r1= %(rwks)col(a); //r1 points to column A on the sheet
r1=data(0,100); //fill r1 with 0, 1, ..., 100;
rwks.addcol(); //add a column to the sheet
range r2 = %(rwks)wcol( rwks.ncols ); //r2 points to the newly added col, last column
r2=r1/10; calculated r2 based on r1, 0, 0.1, ..., 10

Worksheet Column and Cell Substitution

Dataset SubstitutionDataset Substitution

%(workbookName, column) or %([workbookName]sheet, column)
where

• workbookname is workbook shortname
• sheet can be either sheet name, label or index
• column can be either column short name, long name or index

Two syntaxes are supported because before Origin 8, there is only one sheet per workbook, therefore you only need to specify workbookName in 1st argument. Newer versions support multiple worksheets so both book and sheet should be specified in the 1st argument. If you are referring to column on active sheet, you can still use the 1st syntax.

%(Book1, B) //using column short name
%(%H,""Average Temp") //using column long name
winName$="Book1"; %(%(winName$), 2); //using column index

%([Book1]Sheet1, B) //using sheet name and column short name
%([%H]"test result",""Average Temp") //using sheet label and column long name
winName$="Book1"; %([%(str$)]3, 2); //using sheet and column index

For example: the name of column on right side will be substituted and assigned to %A and %B. In the second case, if the named book or sheet does not exist, no error occurs but the substitution will be invalid.

%A = %(%H, B); // Column B of active sheet of active book
type %A; //return column name, if active sheet is 2nd, will return Book1_B@2
type $(%A) //return column values
%B = %([Book1]Sheet3,2); // Column 2 of Book1, Sheet3
type %B; //return column name e.g. Book1_B@3
type $(%B) //return column values

Note: You can use parentheses to force assignment to be performed on the dataset whose name is contained in a string register variable instead of performing the assignment on the string register variable itself.

%A = %(Book1,2);   // Assign column 2 dataset name to %A
(%A) = %(Book1,1); // Copy column 1 data to column 2

Column Calculation with Column Substitution

The ability to get a dataset name from any book or sheet can be very useful in doing calculations involving columns in different sheets, like:

// Sum col(1) from sheet2 and 3 and place the result into col(1) of the active sheet 
col(1)=%([%H]sheet2, 1) + %([%H]sheet3, 1);

// subtract by col "signal" in the 1st sheet of book2 and 
// put result into the active book's sheet3, "calibrated" col
%([%H]sheet3, "calibrated")=col(signal) - %([Book2]1,signal);

The column name should be quoted if using long name. If not quoted, then Origin will first assume short name, if not found, then it will try using long name. So in the example above,

%([%H]sheet3, "calibrated")

will force a long name search, while

%([Book2]1,signal)

will use long name only if there is no column with such a short name.

Cell Contents

%(workbookName, column,row[,format]) or %([workbookName]sheetName, column,row[,format])

• workbookname is workbook shortname
• sheet can be either sheet name, label or index
• column can be either column short name, long name or index
• row is row index
• format, optional, introduced in Origin 8.1
  • no format - full precision
  • W = WYSIWYG based on cell format or column format
  • custom format (see Origin Formats)

Note: Another variation of the cell substitution syntax %([workbookName]sheetName, @WL, column[row][,format]) is supported to get value from a cell. It can't be used to assign a value to a cell.

%(Book1, B, 4) //using column short name
%(%H,""Average Temp", 4) //using column long name
winName$="Book1"; %(%(winName$), 2, 4); //using column index

//using sheet name and column short name
%([Book1]Sheet1, B, 4) 
//using sheet label and column long name
%([%H]"test result",""Average Temp", 4) 
//using sheet and column index
winName$="Book1"; %([%(winName$)]3, 2, 4); 

//using column short name, WYSIWYG
%(Book1, B, 4, W) 
//using column long name, 2 decimal places
%(%H,""Average Temp", 4, .2) 
//using column index, 3 significant digits
winName$="Book1"; %(%(winName$), 2, 4, *3); 

//using sheet name and column short name, WYSIWYG
%([Book1]Sheet1, B, 4, W) 
//using sheet label and column long name, 2 decimal places
%([%H]"test result",""Average Temp", .2) 
//using sheet and column index, 3 significant digits
winName$="Book1"; %([%(winName$)]3, 2, *3); 

For example, if the third cell in the fourth column in the active worksheet of Book1 contains the value 25, then entering the following statement in the Script window will set A to 25 and put double that value into column 1, row 4 of the sheet named "Results" in the same book.

value = %(Book1, 4, 3);
%([Book1]Results, 1, 4) = 2 * value;
%([Book1]Results, 1, 4)=;

To return the contents of a text cell, use a string variable:

type %(Book1, 2, 5); //no $ needed at the end
string strVar$ = %(Book1, 2, 5); //no $ needed at the end
strVar$ = ;

If the name of the workbook/worksheet you are referring to is stored in a worksheet cell, e.g., if the reference workbook name is in column A, 1st cell of current worksheet, you can use following cell formula

=[Book%(col(A)[i]$)]1!A1

to set value of B1 cell to A1 cell in the 1st sheet of reference book.

However, if you want to use “A1” in %() substitution notation, please use double equal sign syntax with string concatenation,

=="[Book" + A1$ + "]1!A1"

Please note that autofill and formula update will also work for such notation.

Assume Book1, Sheet1, column B, row 3, has a numeric value of 12.3456789, but only 2 decimal places are displayed in the worksheet (e.g. (e.g. as set in the Column Properties dialog)).

//Return value with current column format
type "Displayed value: %([Book1]Sheet1,B,3,W)";
//Return full precision value
type "Full precision: %([Book1]Sheet1,B,3)";
//Return custom Origin format
type "3 decimal places: %([Book1]Sheet1,B,3, .3)";
//Or use another expression with @WL option to keep the display format 
type "Displayed value with @WL syntax:  %([Book1]Sheet1, @WL, B[3],W)";

The format character W was introduced in Origin 9.1 to replace the usage of "C" in previous versions (i.e. %([workbookName]sheetName, column, row,C)). The "C" format parameter is no longer supported.

Workbook Information Substitution

As with the Worksheet Column and Worksheet Cell Substitution notation described above, there are two syntaxes for worksheet information substitution since one is for old one-sheet workbook/active sheet in book, while the other is for specified sheet in book.

Column Label Row Information

%(workbookName, @option, columnNumber]) or %([workbookName]worksheetName, @option, columnNumber)

This notation allows access to column label row information.

Those column label row related options from "@" Options for Text Labels are used.

Examples
The script below returns the column name of the first column in the current selection range (for information on the selc1 numeric system variable, see System Variables):

%N = %(%H, @C, selc1);  %N =;

The script below returns the column type for the fourth column in Book 2, Sheet 3:

type %([Book2]Sheet3, @T, 4);

Worksheet Information

%(workbookName, @option [,argument]) or %([workbookName]worksheetName, @option[, argument])
This notation allows access to worksheet info and metadata.

Available options are listed below, including a few from "@" Options for Text Labels.

Examples
The script below returns the worksheet display name

type %(%H, @SN);

In Book2, Sheet3, set some column as YError bars. Select several columns, including some YError bar columns. The script below will return the number of YError columns in selection

type %([Book2]Sheet3, @E#, 2);

If the currently active worksheet window has six columns (XYXYXY) and columns 2, 4, and 5 are selected, then the following script shows the number of the first selected Y column to the right of (and including) the iith column (the third argument):

loop(ii,1,wks.ncols)
{
    type -l %(%H, @YS, ii),;
}

This outputs:

   2,2,4,4,0,0,

Workbook Information

%(workbookName, @option) %([workbookName], @option)
This notation allows access to workbook info and metadata.

It's OK to have [workbookName]Sheet!Column in 1st argument. e.g. [%H]Sheet1!A. Sheet and column part will be ignored.

Available options are listed below, including a few from "@" Options for Text Labels.

Examples
The script below returns the workbook short name

type %(%H, @PN);

Information Storage and Imported File Information

%([workbookName]Sheet!Column, @Woption, varOrNodeName,[format]) where

• Sheet can be sheet short name, long name or index.
• Column can be column short name, long name or index.
• format is optional (see Origin Formats)

Access metadata stored within Origin workbooks, worksheets, or columns, as well as information stored about imported files.

@Woptions explanation

One way to see this syntax at work is to import a file into a workbook, then use the Text tool to start a text label in the gray space to the right of the worksheet columns. Right-click and choose Insert Info Variable. Click the Info tab, expand a node under SYSTEM.IMPORT, choose a variable and click Insert. Here, you'll be able to see the "@W" option in use, as well as the information stored in varOrNodeName. When you close the Insert Variables dialog and click outside the label, the notation is resolved to create a text label that displays workbook metadata.

Examples The following example shows how to access imported file information, extracted variable during import, and user-trees stored on page (book), sheet and column levels.

Use Data: Import from File: Import Wizard.... Pick the 3 S*.dat under \Samples\Import and Export\, and choose Import Mode to Start New Sheets to import 3 files into Book1. These files will be imported into workbook with some user variable extracted. Expand Workbook Organizer.

• page.info node: only contains last imported file related information and variable. @W
• User Tree node: Add User Tree and node on page(book) level. Access by @W
• Imported Files: Add variable on Imported files level. Access by @WFn
• Data Sheets: Add User Tree and node on sheet level. Access by @WS

Access file and extracted variable info. of last imported files

//Returns last imported filename
type %(%H,@W,system.import.filename$);

//Returns last imported filedate using mm/dd/yyyy format
type %(Book1, @W,system.import.filedate, D0);

//Return the BatchNo variable extracted in the last imported file
type %(Book1, @W,user.variables.batchno);

//Return the Sample variable extracted in the last imported file
type %(Book1, @W,user.variables.sampleID$);

In Workbook Organizer, right click User Tree node on the left, choose Add Tree to add a tree named Cate. Select User Tree -> Cate on the left. Add two nodes. a numeric node with name Depth. a text node with name "Direction". Please don't enter Label for tree and nodes since otherwise that label shows in Workbook Organizer instead of name, which will be confusing to user. Set Depth value to be 127.2, set Direction value as East

//Returns Depth and Direction info under User Tree -> Cate
type %(%H,@W,tree.cate.depth, .0); //format to no decimal places
type %(%H,@W,tree.cate.direction$); //use $ at the end for string

In Workbook Organizer, go to Imported Files -> S21-235-07.dat (2nd one) to take a look of trees and nodes of the file.

//Returns file information and extracted variable of 2nd imported file
//Don't add $ at the end of variable or node name
type %(%H,@WF2,info.filename); //get 2nd imported file name
type %(%H,@WF2,header); //don't add $ for string varible
type %(%H,@WF2,Variables.Header.noofpoints); //get extracted varible No. of points info
type %(%H,@WF2,Variables.FileName.SampleID$); //get extracted varible SampleID

In Workbook Organizer, go to Data Sheets -> Trial Run2 (2nd sheet) Right click User Tree node and add a tree named Condition. Add a numeric node named Temp and value 98.6. Add a text node called Result with value Failed

//Returns Depth and Direction info under User Tree -> Cate
type %("Trial Run 2"!,@WS,tree.condition.temp); //skipped book info. if it's current book
type %([%H]2!,@WS,tree.condition.result$); //using sheet index, add $ at the end for string

Create User Tree on column level and access by @WC

With Trial Run 1 worksheet active, double click column D header to open Column Properties dialog. Go to User Tree tab. Right click to add a tree namedMachine. Add a numeric node named Speed and value 42.65. Add a text node called Type with value MD4

//Return Type info under Column User Tree
type %([%H]"Trial Run 1"!"Position",@WC,tree.Machine.type$); //using long name of sheet and column
//If Position not quoted, first search column short name, if not found, search long name
type %([%H]1!Position,@WC,tree.Machine.type$);

//Return Speed info under Column User Tree
//Using sheet and column index, skip book info. if it's current book
type %(1!4,@WC,tree.Machine.speed) 

//using sheet index and column short name, format to show 0 decimal places 
type %(1!D,@WC,tree.Machine.speed, .0)

Legend and Axis Title SubstitutionLegend Substitution Axis Title Substitution

%(PlotSpecifier[, @option, argument1, argument2, ...])
The first argument must start with number or ? to differentiate it from the other %( ) notations, e.g. worksheet substitution notation, where the 1st argument is usually a workbook&worksheet specifier.

All plot related options from "@" Options for Text Labels can be used. Some examples:

Examples

// Display 1st user parameter info. of 1st plot (Y data for 2d by default)
legend.text$ = \l(1) %(1, @LD1); //Ok to skip quotes on right if LHS ends with $

//Display 1st plot's Y dataset long name vs X dataset long name
legend.text$ = "\l(1) %(1Y, @LA) vs %(1X, @LA)";

//Suppose 3 plots are from different sheets, 
//Display worksheet name for each plot
legend.text$="\l(1) %(1,@WS)
\l(2) %(2,@WS)
\l(3) %(3,@WS)"

There is drawback to last script above since all legends must be included in the legend.text$. The legendupdate X-Function provides an easier and more comprehensive way to modify or redraw a legend from Script! E.g. legendupdate mode:=custom custom:=@WS

The %(?Y) for Axis title is a special syntax that allows the text label to default to a pre-specified data plot index (which can be set in Plot Details: Legends/Titles: Data Plot Index for Auto Axis Titles), instead of an index (1, 2, ... n) that you supply. To display Y data long name followed by units in <> as left Y axis title, use:

yl.text$ = %(?Y,@(@LL<@LU>));

Graph legends and axis titles are specialized text labels. For more in-depth discussions of both, see these LabTalk Reference pages:

• Text Label Substitution
• Legend Substitution Notation

Keyword Substitution

The %( ) substitution notation is also used to insert non-printing characters (also called control characters), such as tabs or carriage returns into strings. Use LabTalk keywords to access these non-printing characters. For example,

// Insert a carriage-return, line-feed (CRLF) into a string:
string ss$ = "Hello%(CRLF)Goodbye";
ss$=;     // ANS: 'Hello', 'Goodbye' printed on separate lines
// Can be typed directly
type ss$;
// But use %() substitution when mixed with literals
ty I say %(ss$)%(CRLF)to you;

Show legends horizontally, separated by Tab

legend.text$="\l(1) %(1)%(TAB)\l(2) %(2)%(TAB)\l(3) %(3)";