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.


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


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


enter image description here


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?

  • Updated answer with some troubleshooting suggestions – user79408 May 21 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 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 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 at 4:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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