Excel Text Function

[tarichar]

What formula could I use to convert a string to a text field with 4 characters? In Excel, I use a simple Text() formula: =text(A1,"0000") where the following values would convert if...

• A1 equals 123 and the output adds a leading '0' to '0123'
• A1 equals 001234 and the output removes the leading '0' to '1234'

[cplewis90]

Hello @tarichar,

You would have to do an if statement to check the length and run different formulas depending on what is there. It would look something like the below:

If length([field])>4

then right([field], 4)

else padleft([field],4,"0")

endif

[tarichar]

Thanks for the feedback.

What if i also have values that are greater than 4 characters that I need to stay the same? Example, 123456 needs to remain 123456, not convert to '3456' (using the above if statement). 

[cplewis90]

The if statement would just need to change slightly

If length([field])>4

then trimleft([field],"0")

else padleft([field],4,"0")

endif

[tarichar]

Getting closer, but with a starting value of 000123, the output value would be 123 instead of 0123.

Sorry for all the scenarios, I just keep running into different issues during each data run. 

[cplewis90 · Accepted solution]

One additional modification:

If length([field])>4

then padleft(trimleft([field],"0"),4,"0")

else padleft([field],4,"0")

endif

Let me know if this works. If not I can try something else.

[tarichar]

Nailed it, thanks!