How to Add Leading Zeros in Excel Text Format (11 Methods)

Method 1 – Add Leading Zeros Manually in Excel

Steps:

• Go to cell D5 and manually insert the value of cell C5 with two zeros (00).

• Select range D5:D11 >> go to Data tab >> select Flash Fill from the Data Tools group.

All the corresponding values of range C5:C10 have been copied to range D5:D11 with two leading zeros. We used the Flash Fill so that we do not need to repeat.

You will also see error notifications with the values. We need to remove the error notification.

• Click on the drop-down of the error symbol and select the Ignore Error option from the list.

• No error is shown now.

Method 2 – Use of Apostrophe Operator to Add Leading Zeros in Excel

Steps:

• Go to cell D5 and insert the data of cell C5 with an apostrophe operator and two zeros (00) at the leading position.

• Select range D5:D10 and apply the Flash Fill by pressing Ctrl+E.

You can see error notifications with all cells. You can remove all those errors as shown in Method 1.

Method 3 – Adding Leading Zeros in Excel Text Format with Ampersand

• Go to cell D5 and insert the following formula.

• Drag the Fill Handle to the rest of the cells.

Two leading zeros have been added.

Method 4 – TEXTJOIN Function Add Leading Zeros in Excel Text Format

• Insert the following formula in cell D5 based on the TEXTJOIN function and pull the FillHandle icon.

Leading zeros have been added.

Method 5 – Excel CONCAT Function Adds Leading Zeros

• Input the following formula in cell D5 and drag the Fill Handle

Note:

This CONCAT function is not available in the older Excel versions. You will get the CONCATENATE function in the older versions and the formula will look like this.

Method 6 – How to Add Leading Zeros in Excel Text Format: TEXT Function

• Insert the following formula in cell D5.

• Drag the Fill Handle icon to cell D11.

Method 7 – Excel RIGHT Function to Add Leading Zeros

• Insert the following formula based on the RIGHT function and ampersand operator in cell D5.

• Drag the Fill Handle icon.

Formula Explanation

• “0000”&C5

The ampersand operator joins “0000” and the value of cell C5.

Result: 0000112

• RIGHT(“0000″&C5,5)

The RIGHT function extracts 5 letters or characters from the right side or the end.

Result: 00112

Read More: How to Add Leading Zeros in Excel

Method 8 – Combination of REPT & LEN Functions to Add Leading Zeros

• Insert the following formula in cell D5 and pull the Fill Handle icon.

Formula Explanation

• LEN(C5)

This determines the length of cell C5.

Result: 3

• 5-LEN(C5)

Subtract the length of C5 from 5 to get a result with 5 digits.

Result: 2

• REPT(0,5-LEN(C5))

Repeat zeros (0) based on the result obtained from the subtraction.

Result: 00

• REPT(0,5-LEN(C5))&C5

The ampersand adds zeros with the value of C5.

Result: 00121

Method 9 – Excel BASE Function Adds Leading Zeros

• Insert the following formula in cell D5 and pull the Fill Handle.

The BASE function converts C5 into a 10-base number with 5 digits.

Method 10 – Excel Power Query to Add Leading Zeros

Steps:

• Select range B4:C10 >> Data tab >> click on From Table/Range from the Get & Transform Data group.

• The Create Table window appears. Mark My table has headers option and click OK.

The selected range is also shown.

• The Power Query window and ID column are presenting data in number form.

• Convert data into text format as we need to work with text-formatted data.
• Select the heading of the ID column and press the right button of the mouse.
• Choose the Text option from the Context Menu.

• A pop-up warning is showing to accept the change of column type. Choose to Replace current option.

• We can see data of the ID column are left aligned which means they are in text format.
• Go to Add Column >> Custom Column to add a new column, where we will present data with leading zeros.

• Custom Column window appears.
• Set a name in the New column name.
• Insert the following formula in the Custom column formula section and press OK.

• We can see a new column data with leading zeros.

Method 11 – Excel VBA to Add Leading Zeros

Steps:

• Go to the Leaf Bar and press the right button of the mouse.
• Choose the View Code option from the Context Menu.

• Go to Insert >> Module >> paste the following VBA code on the module.

Sub Add_Leading_Zeros()
Dim Cell_1 As Range
Dim Total_Digits, Required_Zeros As Long
Total_Digits = 5
Set Cell = Selection
For Each Cell_1 In Selection
Required_Zeros = Total_Digits - Len(Cell_1.Value)
If Required_Zeros > 0 Then
Cell_1.Offset(0, 1).Value = String(Required_Zeros, "0") & Cell_1.Value
Else
Cell_1.Offset(0, 1).Value = Cell_1.Value
End If
Next Cell_1
End Sub

• Select range C5:C10 and run the code by pressing the F5 button.

• We can see new data with leading zeros shown in the next column.

How to Remove Leading Zeros in Excel

• We have an ID with two leading zeros in the updated dataset in Column C. Insert the following formula in cell D5 to remove leading zeros.

How to Add Leading Zeros in Excel to Make 10 Digits

• Enter the formula based on the BASE function to get a 10-digit number in cell D5.

• Drag the Fill Handle icon.

We have used 10 in the 3rd argument to get a 10-digit number.

Read More: How to Add Leading Zeros to Make 10 Digits in Excel

Frequently Asked Questions

What to do when leading zeros are not showing in Excel?

Ans: When leading zeros are not showing in Excel, change the format of the required cells into Text.

Things to Remember

• Method 1 to 5 adds a fixed number of leading zeros with the given reference. But the total number of digits is not the same.
• Method 6 to 11 creates a fixed digit number by adding the required number of leading zeros. The number of leading zeros is not fixed, depending on the given reference.

Download Practice Workbook

Related Articles

• How to Write 00 in Excel
• How to Write 001 in Excel

<< Go Back to Pad Zeros in Excel | Number Format | Learn Excel