Add Leading Zeros to SharePoint List Column using Calculated Column

Recently, while working on a SharePoint project for a client, we had one Employee ID column in a SharePoint list containing values such as 5, 45, 254, etc. However, the client wanted the Employee ID to be 0005, 0045, 0234, etc., in this format. This is easily achieved by using a SharePoint calculated column to add leading zeros to the SharePoint list column.

In this tutorial, I will show you how to add leading zeros to a SharePoint list column using a calculated column with various real-world examples.

As a SharePoint developer, you can get this requirement, especially ID columns such as Employee ID, Transaction ID, Product ID, etc. And this will work for both SharePoint lists and libraries.

You can see what it looks like in the screenshot below:

I will show you various ways to add leading zeros to a column using a SharePoint calculated column (i.e., add 0 in front of a number).

Let’s see how to work on this!

Add Leading Zeros to a Column in SharePoint Calculated Column

Here, I have a SharePoint Online list that has a number column as “Employee Number” that contains numeric values like below:

Now, I will show you how to add leading zeros to a calculated column: 0005 instead of just 5.

Add Leading Zeros to SharePoint Number Column

There are two methods you can use to add leading zeros to a SharePoint list column:

• using Text() function
• using Concatenate() function

Now, let me show you two examples for this.

Method 1: Using Text Function

Here, I am using the above list that has the number column: Employee Number. Follow the steps below to create a calculated column based on the Employee Number column.

1. Open your SharePoint list/ library and then click on the + Add column -> Select the See all column types option -> Then, Next Button.

2. It will navigate to the Create Column page, where you can add columns to your SharePoint list. Provide a column name, and choose the data type as Calculated (Calculation based on other columns).

3. Enter the following formula in the Formula box and select the data type returned from this formula to Single line of text. Scroll down the page and click on the OK button.

=TEXT([Employee Number],"0000")

You can replace Employee Number with your SharePoint list column.

Now, the SharePoint calculated column has been created.

4. When you view the SharePoint list, you will see the EMP ID column containing leading zero values. You can see the screenshot below for your reference.

This is one way of adding leading zeros to a list column using a calculated column. Let me now show you the other method.

Method 2: Using the CONCATENATE() Function

We can also add leading zeros to column values by using the CONCATENATE() function in a SharePoint calculated column.

Let’s see how to generate Employee IDs that show leading zeros using the concatenate function in a calculated column.

1. In the formula box of the calculated column, provide the following expression. In the expression below, replace Employee Number with your column.

=CONCATENATE(REPT("0",MAX(0,4-LEN([Employee Number]))),[Employee Number])

Ensure that you select the return data type as ‘Single line of text’, as shown in the screenshot above.

Output:

Add Leading Zeros to SharePoint List Column [Text + Number]

Often, you may need the Employee ID in formats such as EMP0001 or EMP0023, which combines both text and the Employee Number column.

This is also possible by using a calculated column.

Here, I want to add leading zeros to the Employee Number column along with a combination of text.

I will use the same SharePoint list as in Example 1.

By following Example 1, you already know how to create a calculated column in a SharePoint list or library. Let’s start with the following steps.

1. On a Create Column page, provide a column Name and select data type as Calculated column.
2. In the Formula box, insert the formula placed in the code below. Select the data type returned from this formula as a Single line of text. Then, click the OK button.

="EMP"&TEXT([Employee Number],"0000")

3. Go and check your SharePoint list, where you will find the calculated column that adds leading zeros to the text.

You can use the Text() or Concatenate() function to add leading zeros to a SharePoint calculated column. I have used both functions in many SharePoint projects. Do let me know if you still have any questions in the comments below:

You may also like the following tutorials:

• SharePoint Calculated Column Round Up to Decimal Places
• Multiply Columns in SharePoint Calculated Column
• SharePoint Calculated Column Concatenate Function

Bijay Kumar

After working for more than 18 years in Microsoft technologies like SharePoint, Microsoft 365, and Power Platform (Power Apps, Power Automate, and Power BI), I thought will share my SharePoint expertise knowledge with the world. Our audiences are from the United States, Canada, the United Kingdom, Australia, New Zealand, etc. For my expertise knowledge and SharePoint tutorials, Microsoft has been awarded a Microsoft SharePoint MVP (12 times). I have also worked in companies like HP, TCS, KPIT, etc.