Using Calculated Fields to Pad Numbers with Leading Zeros

Just a quick note on one way to put leading zeros in a SharePoint list field using a Calculated Field.  A client wanted a column to show the list item's name, followed by a number that was left-padded with zeros to two digits, like:

Addendum-01

Addendum-02

...

Turns out there's no function (at least that I could find) to do this, but using CONCATENATE, REPT and LEN function, I could do it as follows:

Replace [Item Name] with the field name that contains the name and [Item Number] with the field name that contains the item number.

 =CONCATENATE([Item Name],"-",REPT("0",2-LEN([Item Number])),[Item Number])

The REPT function will repeat a given string a specified number of times.  By subtracting the length of the number from 2, I could tell it how many leading zeros were needed.

One caveat to this is that I'm not sure if it works with numbers longer than the digit count we want (in this case, greater than 99).  I'm not sure of the behavior of REPT with negative numbers passed in.  In my client's, it isn't an issue, but if you need to, you could add an IF function that checks if it is negative and uses zero if it is.