How to Add Leading Zeros to SharePoint Calculated Column?

Zeros at the beginning of a number are known as leading zeros. While leading zeros may not impact the numerical value, they can be important in specific contexts, such as when formatting data or representing specific numerical patterns.

In this SharePoint tutorial, I will show how to add leading zeros to SharePoint calculated column with a simple example.

What is Leading Zeros in SharePoint Online?

Adding leading zeros makes sure that numbers are displayed correctly and sorted numerically. This can be particularly useful when handling numerical data like IDs, dates, or other references in the SharePoint list or library.

The existing column should be in the Number, and the Text should be in the form of Number or Date and Time format to add leading zeros to the calculated column.

leading zeros in sharepoint online

How to Add Leading Zeros to SharePoint Calculated Column

Follow the steps below; I will show examples of adding leading zeros to the SharePoint calculated column.

Example -1: (Using TEXT function for the Number format column or Text is in the form of Number)

1. I have a SharePoint list, namely Employee Details. Which list contains all employee’s data. I must create a calculated column to add leading zeros to the Employee ID column.

add leading zeroes to sharepoint calculated column

2. To add the calculated column, click the + Add column -> select See all column types option -> Next Button.

how to add leading zeros to calculated column in SharePoint

3. Provide a calculated column and select the type of information as the Calculated(calculations based on other columns) option.

how to add leading zeroes to sharepoint calculated column

4. I use the TEXT function here, which changes a value to text in a specific number format. You should provide the column_name and format_text as an argument. The formula for adding leading zeros to the calculated column is:

TEXT(Title,"0000")

I renamed the Title column to Employee ID. So, I mentioned the internal column name.

5. Provide the above formula in the Formula box on Additional Column Settings, and select the data type as Number.

add leading zeroes to the sharepoint calculated column

6. Then click on the OK button.

how to add leading zeroes to the sharepoint calculated column

7. On your SharePoint list page, you will observe that the Employee Number column will be generated by adding leading zeros to the Employee ID column, as shown in the screenshot below.

how to add leading zeros to calculated column in SharePoint online

Example – 2: (Using TEXT function for the Date and Time format column)

In this example, I must add leading zeros to the Joining Date column in the Employee Details list.

1. On the Create Column page, provide a calculated column name as “Employee Joining Date” and select the type of information as the Calculated(calculations based on other columns) option.

2. You can add leading zeros to the Date and Time format column using Format_Text as “dd/mm/yyyy.” The formula is:

TEXT([Joining Date],"mm/dd/yyyy")

3. Provide the above formula in the formula box, and select the data type as Date and Time. Then click on the OK button, as shown in the screenshot below.

add leading zeros to the modern sharepoint columns

4. On the SharePoint list page, You can observe the Employee Joining Date column will be created by adding leading zeros to the Joining Date column. You can observe the differences between the two columns, as shown in the screenshot below.

add leading zeros to the sharepoint online

Example -3: (Using CONCATENATE, MAX, REPT, and LEN functions)

Using CONCATENATE, MAX, REPT, and LEN functions, You can add leading zeros to the Number format column, or Text should be in the Number format column.

In this example, I need to add leading zeros by creating a calculated column to the Employee ID column in the Employee Details list.

1. On the Create Column page, provide a calculated column name as “Employee ID Number” and select the type of information as the Calculated(calculations based on other columns) option.

2. The formula for adding leading zeros to the SharePoint calculated column is:

CONCATENATE(REPT("0",MAX(0,5-LEN([Title]))),[Title])

Where,

  • Title: Internal name of Employee ID column in Employee Details List. You can provide the internal name of an existing column name.
  • LEN function: The LEN function counts the number of characters in a text string. The syntax is “LEN(Text).”
LEN(12)                                   // Output = 2
  • MAX function: Returns the largest value in a list of values. The syntax is “MAX(Number1, Number2, Number3,…..).”
MAX(0,5-2)                          // Output = 3
  • REPT function: Use the REPT function to duplicate a text string a specified number of times. The syntax is “REPT(Text, Number_of_Times).”
REPT("0",3)                          // Output = 000
  • CONCATENATE function: This fiction joins multiple texts into one string. We can connect up to 30 text items. The Syntax is “CONCATENATE(Text1,Text2,Text3,….).”
CONCATENATE("000",12)                       // Output = 00012

3. Provide the above formula and select the Number data type in the additional column settings. Then click on the OK button, as shown below screenshot.

how to add leading zeros to sharepoint column

4. The Employee ID Number column will be credited by adding leading zeros to the Employee ID column on the SharePoint list page. Refer to the attached screenshot below.

add leading zeros to sharepoint online

These are three examples of adding leading zeros to the SharePoint calculated column.

In this SharePoint tutorial, I showed all the possible ways to add leading zeros to the SharePoint calculated column with suitable examples.

I hope this information will be helpful when you include leading zeros in your SharePoint list or library.

Moreover, you may also like some more SharePoint articles: