Forum Discussion
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!
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.
- Not to complicate this too much. But, my text formatted column actually sorted correctly in our old 2010 sharepoint server. It stopped working when we moved to sharepoint online. I was thinking the issue was because it was text and not a number, but the fix was to index this column. This is a large library (~15,0000 files). Once I indexed the column, now it will sort the ascending/descending correctly. Thank you for all the replies!
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 UserYou 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.
