2.3.2.3.2 %( ) Substitution - String Substitution

%( ) Substitution

String Expression Substitution

While LabTalk commands often accept numeric expressions as arguments, none accept a string expression. So if a string is needed as an argument, you have to pass in a string variable or a string expression using the %( ) substitution to resolve run-time values. The simplest form of a string expression is a single string variable, like in the example below:

string str$ = "Book2";         
win -o %(str$) {wks.ncols=;}

Worksheet Column and Cell Substitution

The following notation allows you to access worksheet cells as a string as well as to get the column dataset name from any workbook sheet. Before Origin 8, each book had only one sheet so you could refer to its content with the book name only. Newer versions support multiple worksheets, so we recommend that you use [workbookname]sheetNameorsheetIndex to refer to a specific sheet, unless you are certain that the workbook contains only one sheet.

Column dataset name

Syntax Description
%(workbookName, column) Older notation for active sheet of named book
%([workbookName]sheetNameorsheetIndex, column) Post-Origin 8 book sheet notation using either sheet name or sheet index

For example:

%A = %(%H, 2);           // Column 2 of active sheet of active book
type %A;
%B = %([Book1]Sheet3,2); // Column 2 of Book1, Sheet3
type %B;

In the above example, the name of the dataset in column 2 in the active worksheet is substituted for the expression on the right, and then 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.

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);   // Get column 2 dataset name
type %A;           // Types the name of the dataset
(%A) = %(Book1,1); // Copy column 1 data to column 2

To return individual cell contents

Syntax Description
%(workbookName, column, row) references the active sheet in the named book
%([workbookname]sheetname, column, row) specifies book and sheet


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.

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


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

string strVar$ = %(Book1, 2, 5); // Note : No end '$' needed here
strVar$ = ;

Formatting the returned value using "W" and "@WL"

  • Before Origin 8.1, you could only specify column and row index of the active sheet and numeric cells always returned full precision.
  • Beginning with 8.1, you can specify book and sheet an optional format argument allows you to return numbers in a custom format (see Origin Formats).
    %([workbookname]sheetname, column, row[,format])
  • Another variation of the syntax -- one that is necessary when working with range variables -- allows return of 3 levels of formatting: cell format, custom format or the full precision value stored in the cell.
    %([workbookName]sheetName, @WL, column[row][,format])

... where the optional format argument takes one of the following values:

Argument Description
W Return in the cell format.
custom format Return the full precision value of the cell, modified by one of these Origin Formats.
omitted Return the full precision value of the cell.


Examples:

Assume Book2, Sheet3, col(Signal), row 3, has a numeric value of 12.3456789, but only 2 decimal places are displayed (e.g. set in the Column Properties dialog).

//format string W to use current column format
//Should return 12.34
type "Col(Signal)[3] displayed value is %([Book2]Sheet3,Signal,3,W)";
//full precision if format not specified
A=%([Book2]Sheet3,Signal,3);
A=;// shows 12.3456789
//Or use other format notations
type "Showing 3 decimal places:%([Book2]Sheet3,Signal,3,.3)";
//Or use another expression with @WL option to keep the display format 
type "Col(Signal)[3] displayed value is %([Book2]Sheet3, @WL, Signal[3],W)";
//full precision if format not specified
B=%([Book2]Sheet3,@WL,Signal[3]);
B=;// shows 12.3456789

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.

Calculation Involving Datasets from Another Sheet

The ability to get a dataset name from any book or sheet (Dataset Substitution) 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.

Worksheet Information Substitution

Similar to worksheet column and cell access with substitution notation, the @ Substitution (worksheet info substitution) make uses of the @ character to differentiate from a column index or name in the 2nd argument. This notation allows access to worksheet info and metadata.

As with the Worksheet Column and Cell Substitution notation described above, there is an older still-supported syntax for a one-sheet window but we recommend using the newer notation (introduced in Origin 8):

To return information on the worksheet

Syntax Description
%(workbookName, @option, columnNumber) references the active sheet in the named book
%([workbookName]worksheetName, @option, columnNumber) specifies book and sheet


Here, option can be one of the following (note that parameter columnNumber is omitted for some options):

Option Return Value
@# Returns the total number of worksheet columns.
@CS Returns the column index of the first selected column to the right of (and including) the columnNumber column, regardless of column designation.
@E# If columnNumber = 1, returns the number of Y error columns in the worksheet. If columnNumber = 2, returns the number of Y error columns in the current selection range. If columnNumber is omitted, columnNumber is assumed to be 1.
@H# If columnNumber = 1, returns the number of X error columns in the worksheet. If columnNumber = 2, returns the number of X error columns in the current selection range. If columnNumber is omitted, columnNumber is assumed to be 1.
@P Use with %H to return the Project Explorer (PE) path of the active window (%(%H, @P)).
@PC Page Comments
@PCn Page Comments, the nth line only
@PL Page Long Name
@PN Page short Name
@SN Sheet Name
@SC Sheet Comments
@OY Returns the offset from the left-most selected Y column to the columnNumber column in the current selection.
@OYX Returns the offset from the left-most selected Y column to the columnNumber Y column counting on Y columns in the current selection.
@OYY Returns the offset from the left-most selected Y column to the columnNumber X column counting on X columns in the current selection.
@T Returns the column type. 1 = Y , 2 = disregarded, 3 = Y error, 4 = X , 5 = label, 6 = Z, and 7 = X error.
@X Returns the index number of the worksheet's first X column. Columns are enumerated from left to right, starting from 1. Use the syntax:  %(worksheetName, @X);
@Xn Returns the column short name of the worksheet's first X column. Use the syntax:  %(worksheetName, @Xn);
@Y Returns the offset from the left-most selected column to the columnNumber column in the current selection.
@Y- Returns the column number of the first Y column to the left. Returns columnNumber if the column is a Y column, or returns 0 when the Y column doesn't exist. Use the syntax:  %(worksheetName, @Y-, ColumnNumber);
@Y# If columnNumber = 1, returns the number of Y columns in the worksheet. If columnNumber = 2, returns the number of Y columns in the current selection range. If columnNumber is omitted, columnNumber is assumed to be 1.
@Y+ Returns the column number of the first Y column to the right. Returns columnNumber if the column is a Y column, or returns 0 when the Y column doesn't exist. Use the syntax:  %(worksheetName, @Y+, ColumnNumber);
@YS Returns the number of the first selected Y column to the right of (and including) the columnNumber column.
@Z# If columnNumber = 1, returns the number of Z columns in the worksheet. If columnNumber = 2, returns the number of Z columns in the current selection range. If columnNumber is omitted, columnNumber is assumed to be 1.

The @Options in the Text Label Options page may also be also be used for returning information on the worksheet.

Information Storage and Imported File Information

These @W variables access metadata stored within Origin workbooks, worksheets, and columns, as well as information stored about imported files.

String substitution systemdotimport.png
Syntax Description
%(workbookName]worksheetName, columnName, @Woption, varOrNodeName) specify book, sheet or column, as needed.
%([workbookName]worksheetName!columnName, @Woption, varOrNodeName) same as above but using range-style notation

Example:

filename$ = %(Book1,@W,system.import.filename$);
filename$=; // returns S15-125-03.dat

filename$=%([Book1]"Trial Run 1"!,@W,system.import.filename$);
filename$=; // returns S15-125-03.dat
Option Return Value
@W Returns the information in varOrNodeName; the variable is understood to be located at the workbook level, which can be seen in workbook Organizer. When it is used, there is no need to specify worksheetName!ColumnName.
@WFn Returns the information in varOrNodeName for the nth imported file. The variable can be seen in the workbook Organizer.
@WS Returns the information in varOrNodeName; the variable is understood to be located at the worksheet level, which can be seen in workbook Organizer. When it is used, there is no need to specify ColumnName.
@WC Returns the information in varOrNodeName; the variable is understood to be located 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 of @ Substitution

This script 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, @col, selc1);  %N =;

This script returns the Project Explorer path of the active window:

%P = %(%H, @P); %P=;

The following line returns the active page's long name to a string variable:

string PageName$ = %(%H, @PL);

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

string colType$ = %([Book2]Sheet3, @T, 4);  
colType$=;

An import filter can create a tree structure of information about the imported file that gets stored with the workbook. Here, for a multifile import, we return the number of points in the 3rd dataset imported into the current book:

%z=%(%H,@WF3,variables.header.noofpoints);
%z=

If the currently active worksheet window has six columns (XYYYYY) 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 column whose index is equal to columnNumber (the third argument):

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

This outputs:

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

Legend and Axis Title Substitution

Graph legends and axis titles also use %( ) substitution. The first argument must be an integer to differentiate it from other %( ) notations, where the first argument is a worksheet specifier (e.g. plot index). In its most common and basic form, the legend and axis title substitution syntax is:

%(PlotIndex[, @option])

where PlotIndex is the index of the data plot in the current layer, or a question mark ("?"):

  • The variable PlotIndex might be followed by more options, such as a plot designation character (X, Y or Z) associated with the data plot; when not specified this is assumed to be Y.
  • The @option parameter is an optional argument that controls the legend contents. For example:
// In the legend of the current graph layer ...
// display the Long Name for the first dependent dataset.
legend.text$ = %(1Y, @LL)  

// Equivalent command (where, Y, the default, is understood):
legend.text$ = %(1, @LL)
  • Alternatively, to display the Short Name for the second independent (i.e., X) dataset in the legend, use:
legend.text$ = %(2X, @LS)

This style of legend modification is limited in that it only changes a single legend entry, but the syntax is good to understand, as it can be used in the Plot Details dialog.

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:

The legendupdate X-Function provides an easier and more comprehensive way to modify or redraw a legend from Script!

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$) you say;