I have a column of numbers up to 11 digits long. I would like them all to be 11 digits with leading zeroes when needed, so for instance, 94732 would get converted to 00000094732. Does this make sense? how do I do this?

[Trifacta_Alumni · Accepted solution]

This can be done in two steps (well, technically one, but I'll break it up into two for clarity). The first thing you'll want to do is merge 11 0s to your column.

You can do this with the following step:

Transformation: set

Columns: your_column

Formula: Merge(['00000000000', your_column])

This will give you a column with values ranging in length from 11 characters (in the case of an empty row) to 11 + your longest string of numbers.

Then, you'll want to use the RIGHT() function to trim to the right most 11 digits. This step will look like this:

Transformation: set

Columns: your_column

Formula: RIGHT(your_column, 11)

which will have this effect:

Hope this helps :)

[4bc392f306592b8ab22c]

Nice! I was able to do this in one step by putting the MERGE inside the right function, so it looked like RIGHT(MERGE(['00000000000', order_number]), 11) 😀