Forum Discussion

iwannabfishn's avatar
iwannabfishn
Copper Contributor
Feb 28, 2023
Solved

Format a number column to contain 3 numbers

I have a number column in sharepoint online that contains (1,10, and 100). I need this to show as (001, 010, and 100). How can I format this column to display these leading 0s? Thanks!
SharePoint Online
  • iamsarah's avatar
    iamsarah
    Feb 28, 2023

    iwannabfishn 

    You cannot do it with datatype Number. You can create a calculated column and then append 0 or 00 to the beginning.

     

    Example: NumberColumn is the column with type Number

    and CalNumber is the calculated column with type Text. Here is the formula for the calculated column:

     

    =IF(LEN(NumberColumn)=1,"00"&NumberColumn,IF(LEN(NumberColumn)=2,"0"&NumberColumn))

     

    If my response helped you, please Like and Mark as Best Response. 

RobElliott's avatar
RobElliott
Silver Contributor
Mar 01, 2023

iwannabfishn iamsarah is not entirely correct when she says it cannot be done with a number column; it can with a small bit of JSON column formatting and doesn't require a calculated column. The JSON is:

 

 

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

 

 

 

My padStart column is a number column. And the raw numbers I've input are 75, 120, 3, 110, 995, 25 and 3:


Rob
Los Gallardos
Microsoft Power Automate Community Super User

Resources