Leading Zeros to organise project folders within columns

Question

Hi I am a complete newbie and I want to organise the Projects by JobPac numbers which start with leading Zeros - 0001, 0002 is there some way I can do this? Or would it be best to ask the JobPac team to renumber and drop leading zeros? I found an answer from 2014 which suggested this- concat(substring('00', 1, 3 - string-length(field)), field) However I tried but it didn't work displayed 5 not 0005.

Answer 1

If you only want to add 3 leading zeros, JSON formatting will help you do that.

How to start using JSON formatting.

A bit more explanation: The JSON I provide simple add 3 leading zeros on the original pac number. So no need to worry about the input. If user enters, say '11', then in the list you will see '00011'.

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "=('000'+'@currentField')"
}

The original code number is M123456789.

Answer 2

You could create a calculated column that references the JobPac number field and use something like this for the formula:

=Text([jobPacField],"0000")

Update

If you're having difficulty with the formula, it might be easier to double click on the column from the "Insert Column" box.

Another thing that might cause an issue is pasting the double quotes from this website. Perhaps try typing the formula instead of copying & pasting?

Answer 3

You want the new padStart operator

From https://docs.microsoft.com/en-us/sharepoint/dev/declarative-customization/formatting-syntax-reference

padStart: pads the current string with another string until the resulting string reaches the given length. The padding is applied from the start of the current string.

"txtContent":"=padStart('DogFood', 10, 'A')" results in AAADogFood
"txtContent":"=padStart('DogFood', 10, 'AB')" results in ABADogFood
"txtContent":"=padStart('DogFood', 5, 'A')" results in DogFood

The way I have implemented it on a number column is as below:

{
   "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
   "elmType": "div",
   "txtContent":"=padStart(toString(@currentField), 4, '0')"
}