2.3.2.2.2 %( ) Substitution - String Substitution

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

Syntax Description Examples
%(workbookName, column) column on active sheet of book
%(Book1, B) //using column short name
%(%H,""Average Temp") //using column long name
winName$="Book1"; %(%(winName$), 2); //using column index
%([workbookName]sheet, column) column on specified sheet of book
%([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 Substituion

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.

Syntax Description Examples
%(workbookName, column, row) cell on active sheet in the named book, full precicion
%(Book1, B, 4) //using column short name
%(%H,""Average Temp", 4) //using column long name
winName$="Book1"; %(%(winName$), 2, 4); //using column index
%([workbookname]sheetname, column, row) cell on specifies book and sheet, full precicion
//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); 
%(workbookName, column, row, format) cell on active sheet in the named book, with format
//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); 
%([workbookname]sheetname, column, row, format) cell on specifies book and sheet, with format
//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.

Syntax Description
%(workbookName, @option, columnNumber) column info. of the active sheet in the named book
%([workbookName]worksheetName, @option, columnNumber) column info. of specified book and sheet

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

Option Description
@C Returns column short name
@D Returns column dataset name
@L Returns column long name
@LA Returns column long name. Returns short name if no long name
@LCn Returns column comments (nth row). Returns 1st line comment if n is skipped
@LDn Returns contents of column's nth user-defined parameter. n=1 if skipped
@LD"paraname" Returns contents of column's user-defined parameter with name "paraname"
@LG Returns column long name (unit). Returns short name and unit if no long name
@LPn Returns contents of column's nth system parameter. n=1 if skipped
@LS Returns column short name. same as @C
@LU Returns column unit
@Fn Returns nth row of column Filter information. Return full filter info. if n is skipped
@T Returns column type. 1 = Y , 2 = disregarded, 3 = Y error, 4 = X , 5 = label, 6 = Z, and 7 = X error.
@U Returns column long name (unit). Returns short name and unit if no long name. Same as @LG,

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.

Syntax Description
%(workbookName, @option[,argument]) worksheet info. of the active sheet in the specified book
%([workbookName]worksheetName[, @option, argument]) worksheet info. of specified book and sheet


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

Option Argument Description
@# Not needed Returns the total number of columns in worksheet.
@CS columnNumber Returns the column index of the first selected column to the right of (and including) nth column. Return 0 if not found
@E# Optional If argument = 1, returns the number of Y error columns in the worksheet. If argument = 2, returns the number of Y error columns in the current selection range. If argument is omitted, it's assumed to be 1.
@H# Optional If argument = 1, returns the number of X error columns in the worksheet. If argument = 2, returns the number of X error columns in the current selection range. If argument is omitted, it's assumed to be 1.
@OY columnNumber Returns the offset from the 1st selected Y column to the nth Y column to its right in selection. The nth Y must be in selection as well.
@OYX columnNumber Returns the offset from the left-most selected Y column to the n+1th selected X column to its right in selection. The nth X must be in selection as well.
@OYY columnNumber Returns the offset from the 1st selected Y column to the nth Y column to its right in selection. The nth Y must be in selection as well. Equivalent to @OY
@SCn Not needed Returns the nth row of worksheet comments. If n is skipped, it's the 1st row of comments
@SN Not needed Returns worksheet display name
@SNL Not needed Returns worksheet label
@X Not needed Returns the column index of the first X column in worksheet,
@Xn Not needed Returns the column short name of the first X column in worksheet.
@Y- Needed Returns the column index of the closest Y column to the left of nth column. Returns 0 if not found.
@Y# Optional If argument = 1, returns the number of Y columns in the worksheet. If argument = 2, returns the number of Y columns in the current selection range. If argument is omitted, it's is assumed to be 1.
@Y+ Needed Returns the column index of the closest Y column to the right of nth column. Returns 0 if not found.
@YS Needed Returns the column index of the first Y column in selection range to the right of (and including) the nth column.
@Z# Optional If argument = 1, returns the number of Z columns in the worksheet. If argument = 2, returns the number of Z columns in the current selection range. If argument is omitted, it'ss assumed to be 1.

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.

Syntax Description
%(workbookName, @option) Workbook info of named book

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.

Option Description
@PN Returns workbook window short name.
@PL Returns workbook window long name.
@PCn Returns the nth line of workbook window comments. @PC for all comments

Examples
The script below returns the workbook short name

type %(%H, @PS);

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.

String substitution systemdotimport.png
Syntax Description
%([workbookName]sheet!column, @Woption, varOrNodeName) Return variable or Node information without format
%([workbookName]sheet!column, @Woption, varOrNodeName, [format]) Return variable or Node information with format

@Woptions explanation

Option Return Value
@W Returns the information in varOrNodeName under page.info or User Tree in the Workbook Organizer. No need to specify Sheet!Column.
@WFn Returns the information in varOrNodeName under the nth Imported Files in the Workbook Organizer. No need to specify Sheet!Column.
@WS Returns the information in varOrNodeName of the nth sheet under Data Sheets in the Workbook Organizer. No need to specify Column.
@WC Returns the information in varOrNodeName of User Tree at the column level, which can be seen in the Column Properties dialog.


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.

LT Information Storage Substitution.png


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

PlotSpecifier Description Example
<plotIndex> nth plot in current layer, Y info for 2D and Z info for 3D  %(3) //current layer, 3rd plot's Y info
[layerIndex.]<plotIndex> nth plot in specified layer, Y info for 2D and Z info for 3D  %(2.3) // 2nd layer, 3rd plot's Y info.
<plotindex><plotdesignation> X, Y or Z of nth plot in current layer.  %(1X) //current layer, 1st plot's X info
[layerIndex.]<plotindex><plotdesignation> X, Y or Z of nth plot in specified layer.  %(2.1X) //2nd layer, 1st plot's X info
?<plotdesignation> X, Y or Z of auto assigned plot index in current layer  %(?Y) //current layer, 1st plot Y info by default, e.g. see Y axis title
[layerIndex.]?<plotdesignation> X, Y or Z of auto assigned plot index in specified layer  %(2.?Y) //2nd layer, 1st plot Y info by default


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

@Option Arguments Description Example
@C NA Column Short Name. Equivalent to @LS. %(1,@C)
@LD"name" NA The user-defined parameter of specified name. %(1,@LD"RunNo")
@LG NA Long Name (if not available then Short Name) and Units (if present). Equivalent to @U. %(1,@LG)
@V RowIndex The data value at specified row index in plot's Y data
  •  %(1Y, @V, 2)
    the 1st plot's Y data's 2nd row value
@WT ColIndex/ColName, RowIndex[, format] The sheet cell value specified by the column index (or short name) and row index, with optional format (see Origin Formats).
  • %(1, @WT, 2, 3)
    the cell value at column 2, row 3 in the source sheet (worksheet, matrix) of the 1st data plot.
  • $( %(1, @WT, 2, 3,.4 ) * 100, .2 )
    Because it's numeric, get column 2, row 3 value, keep 4 decimal places, times by 100, then format to show 2 decimal places
  • %(1, @WT, B, 3)
    the cell value at column B, row 3 in the source worksheet of the 1st data plot.
  • %(1, @WT, B, 3, .0)
    the cell value at column B, row 3 in the source worksheet of the 1st data plot, show 0 decimal places.
@WT ColIndex/ColName, ColLabelRowCharacter[, format] The worksheet cell value specified by the column index (or short name) and column label row character, with optional format for numeric value (see Origin Formats).
  • %(1, @WT, B, C)
    Similar to B[C]$, this will get the Comment from column B, in the source worksheet of the 1st data plot.
  • %(1, @WT, 2, D1, *3)
    get 1st user-defined parameter info. from 2nd column, in the source worksheet of 1st data plot. Suppose it's numeric value, format to show 3 significant digits

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:

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)";