RIGHT TM1 Function: Use and Syntax

The RIGHT function from Excel does not exist in TM1, but here’s how to do it anyway! We need to use a combination of SUBST and LONG functions instead. It is really quite easy and below we will give you the exact syntax.

Where you have a formula in Excel in A2 that is = RIGHT( A1, 4), where the contents of A1 is the string “ExploringTM1”, the formula will return the last 4 characters, i.e. “gTM1”.

Syntax of TM1 RIGHT Function

To achieve the same result in a TM1 Turbo Integrator process we need to use the SUBST and LONG functions. A full discussion on SUBST can be found here and LONG here, however briefly, the formula to replicate the Excel function illustrated above would be:

SUBST ( 'ExploringTM1', LONG ( 'ExploringTM1') -4 +1, 4);

In other words:

  • Take the string (or the variable referred to that contains the string) and set the starting at position as the length of the string (‘ExploringTM1’, which is 12 characters).
  • Subtract 4 (for the length you want to return) and add 1 (so the starting position is actually the 9th character – the “g”, in ExploringTM1),
  • Then take the next 4 characters (including that first character),

Which will return ‘gTM1’.

This applies to both Rules and Turbo Integrator processes.

The LEFT Function in TM1

If you’re looking for a LEFT function, well there is not one of them either. You need to use SUBST, start at position 1 and go for x positions.

You might also like