All datasets should have a unique identifier in the form of an ID number for orders for example - but unfortunately this isn't always the case, and sometimes when there is, they aren't all the same length. How useful!

(ID Numbers being in the same format with the same amount of characters is easier for both visual and practical purposes, and can be left as a string).

To make this a bit cleaner we can apply a formula that makes them all the same length, and has zeroes in front of numbers that aren't the desired character length.

First we need to add a buffer amount of zeroes in front of the ID, and then take the first n number of digits we want the ID number length to be. We do this by using the formula:

Left(PadLeft([ID Number], 9, '000000000'),9).

Firstly, the PadLeft function adds the characters in front of the [ID Number] field, being '000000000', which allows us to make sure that all the id numbers have enough 0's to meet our required length.

PadLeft (String, len, char)

We then use the Left function, which takes the first n number of characters from the left, which we can set to be our desired length, which is 9 in this case.

Left(String, len)

This gives us the desired output:

If you want more practice at this method, try the preppin' data challenge on just this: https://preppindata.blogspot.com/2022/11/2022-week-44-creating-order-ids.html