0

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.

3 Answers 3

0

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.

enter image description here

0

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

=Text([jobPacField],"0000")

enter image description here

Update

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

enter image description here

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

4
  • Updated answer with some troubleshooting suggestions
    – user79408
    May 21, 2021 at 4:02
  • Thanks I have done that and typed it, changed it, added from insert column =Text(intField,"000") and I've tried =Text[intField,"000"] and i get - The formula contains a syntax error or is not supported. any ideas please?
    – dkreate
    May 21, 2021 at 4:19
  • What type is your [intField] column? Mine is a Number type. From what I can tell, that formula should definitely work for both "Number" and "Single line of text" columns.
    – user79408
    May 21, 2021 at 4:37
  • yup it's a Number type. I have no idea what I'm doing wrong. I think I have set up the column I want the new one to calculate from wrong as it is green in the Settings menu.
    – dkreate
    May 22, 2021 at 4:27
0

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')"
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.