Exploring TM1 - a Chartertech Company
Close this search box.

SUBST TM1 Function (like Excel MID or SQL Server SUBSTR): How to Use and Syntax

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


  • 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 in a TI or STR in a rule 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.

  • This field is for validation purposes and should be left unchanged.

Post Sections

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *

Log In