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.
