The SUBST function is essentially the same as the Excel MID function or the SQL Server SUBSTR function, in that it allows you to select a group of characters of  a specified length from a larger string of characters.

Syntax of SUBST

The syntax is SUBST(string, beginning, length); where

• string, is the text string that you want to take a slice out of
• beginning, is the numeric position in string where you want to start the slice
• length, is the number of characters representing the length of the slice

Usage of SUBST

An example is SUBST(‘exploringtm1’, 4,3); which will return ‘lor’.

Typically this will be used with a string variable as the first operator, such as:

sVariable = ‘exploringtm1’;

SUBST(sVariable, 4,3);

would also return ‘lor’.

It is important to note that the first operator must be a string.  So if you have a source that is numeric, for example 12345 and you want to extract the last two digits, then you first need to convert this number to a string using numbertostring and then use SUBST on the result of that.

SUBST can be used in both TM1 rules and TurboIntegrator processes

Note re Excel MID and SQL Server SUBSTR

Please note that both MID and SUBSTR do not work in TM1, you must use SUBST.