Excel TEXT Function in Tulip

Product Suggestions
1

Hi Tulip Team,

I’m looking for the functionality provided by the TEXT function in Excel for the expression editor in Tulip triggers. Currently, I am using a series of IF statements paired with LEN to achieve this. Is there a better way of doing this? Ideally the TEXT functionality in Excel is what I’m seeking in which you can simply add leading zeros (along with many other functionalities the TEXT function provides!)

image
image750×112 23.7 KB

image
image737×166 9.47 KB

2

Hi @iamwout

I usually do it like this:

Right( '0000’ + @variable.myVar, 4 )

The only downside is, when your Variable is longer then the 4 characters, it will get cut.
(As your function also does).

If you want the exact same thing like your excel example, you need to add the if:

if(len(@variable.myVar) < 5 , Right( '0000' + @variable.myVar, 4) , @variable.myVar)

But there is no need to count the length and add the specific amount of ‘0’ for each case.

1 Like
3

Hi Thorsten,

I do need to count the length because I am incrementing the generated serial number. I am not seeking to simply add leading zeroes but how many leading zeroes I need for the existing serial number + 1 . So in other words, using your example, I don’t know what length @variable.myVar is.

I suppose I could get the length upstream, set that as a variable, and then use it in a RIGHT function like you are dynamically, but I still need to get the LEN.

Anyway, regardless, my point was a feature request for maybe a more intuitive function but I’m sure you get a lot of requests to add native Excel functions to Tulip expression editor and maybe that’s not the direction you are choosing to pursue.

4

I see, that you increment the number, but there is still no need to differentiate 5 cases.
There is also no need to check the length…

'CPXR' + Right( '00000’ + ((TEXTTOINTEGER(RIGHT(@variable.myVar, 5)) + 1)), 5 )

would do the same thing…

image
image1100×190 7.43 KB

My first example was just covering the need to fill up with 0 as the excel function does.

keep in mind. This will fail when reaching 99999, because 100000 would be cut to 00000

However you could also catch that one.

——————
Edit:

Sorry If there is any confusion. I just wanted to help. I’m not from Tulip or so, so I don’t get any such requests.

By the way. Your expression with the Excel Text function would look like this:

'CPXR' + text((TEXTTOINTEGER(RIGHT(@variable.myVar, 5)) + 1),’00000’)

And without:
'CPXR' + Right( '00000’ + ((TEXTTOINTEGER(RIGHT(@variable.myVar, 5)) + 1)), 5 )

This is a difference of five characters (if its actually only called “text”) and not a ton of a difference.
I’m concerned that having too many functions will make the (already cluttered) Expression Editor even more confusing.