Freigeben über

Using Calculated Fields to Pad Numbers with Leading Zeros

  • Artikel

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.

Comments

  • Anonymous
    September 12, 2010
    My current project requires ability to display choice column value via color or image associated with a choice But Sharepoint standard packaged misses that control I am looking for available solutions on market I came across http://sharepointfields.com Does anybody has experiece using it?

  • Anonymous
    March 08, 2011
    Thanks for this, its incredibly fustrating to setup a list and see it order things like this 1,10,11,2,3,4..etc. This saved me doing it through dataview parts and xsl, which is always a life saver

  • Anonymous
    June 14, 2012
    GREAT POST!!!! Thank you

  • Anonymous
    February 15, 2013
    Brill. Saved me a lot of time. I needed to create a combined foreign key for use in a linked data source. Thank you.

  • Anonymous
    October 27, 2013
    Worked for me, thanks very much... Actually, i updated the formula because i wanted to pad to a maximum of two leading zeroes. May help other people: =CONCATENATE("OES-",REPT("0",MAX(0,3-LEN(ID))),ID)

  • Anonymous
    June 03, 2014
    Wipii+ID number => e.g. Wipii1000, Wipii0100, Wipii0010, Wipii0001 =IF(ID>999;CONCATENATE("Wipii";ID);IF(ID>99;CONCATENATE("Wipii0";ID);IF(ID>9;CONCATENATE("Wipii00";ID);CONCATENATE("Wipii000";ID))))