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

In Microsoft Excel, leading zeros can be extremely useful in a variety of applications. A leading zero is any “0” digit in a number string that comes before the first nonzero digit. Sometimes we may have come across a circumstance when we needed to add a leading zero in Excel. In this article, we will demonstrate how to add leading zeros in Excel text format.

What Are Leading Zeros in Excel?

Leading zeros are the zeros that are used to fill up empty spaces. Usually, zeros are not allowed to show in front of the number in Excel. That time, you can convert the number into text and you will be allowed to add zeros. Leading zeros are useful in different fields like you want to represent a phone number or an Id number of fixed digits. Like, there are 20 students in a class, and ID starts from 1 to 20. So, for 1 to 9 IDs will be single digits, and the rest are double digits. To show all the IDs of the same digits we add a leading zero for 1 to 9 and all IDs are on the same digits now.

How to Add Leading Zeros in Excel Text Format: 11 Methods

There is a wide range of options for how to add leading zeros in Excel text format. We will discuss 11 methods in the section below.

Here, we will use the following dataset that consists of some employees’ names and IDs. We will add leading zeros with IDs that are in Text format.

1. Add Leading Zeros Manually in Excel

In this section, we will put zeros before the numbers in text format manually. After that, we will use the Flash Fill feature. Look at the below section for details. Here, we will add two (2) leading zeros.

📌 Steps:

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

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

Now, 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 perform the previous task repeatedly.

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.

• You can see no error is showing now.

2. Use of Apostrophe Operator to Add Leading Zeros in Excel

In this section, we will use the apostrophe operator to add leading zeros in Excel. This is also applicable for cells that are not in text format. Look at the section below.

📌 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. This will follow the format of cell D5 to the selected cells.

You can see error notifications with all cells. You can remove all those errors as shown in the previous section.

Read More: Excel VBA: Format Cell as Text

3. Adding Leading Zeros in Excel Text Format with Ampersand

In this section, we will use a formula to add leading zeros in Excel text format using the ampersand (&) operator.

• Go to cell D5 and insert the following formula.

• Then, pull the Fill Handle icon.

We can see two leading zeros have been added.

4. TEXTJOIN Function Add Leading Zeros in Excel Text Format

Here, we will use the TEXTJOIN function. This function is used to join multiple texts or numbers. We also have the opportunity to use a delimiter using this function. Here, this function will add leading zeros to the existing data in Excel.

• Insert the following formula on cell D5 based on the TEXTJOIN function and then pull the Fill Handle icon.

We can see leading zeros have been added without any hassle.

5. Excel CONCAT Function Adds Leading Zeros

Usually, the CONCAT function is used to combine multiple texts in Excel. This function is available in the latest versions of Excel. Here, we are using the CONCAT function to add leading zeros with text format.

• Input the following formula on cell D5 and drag the Fill Handle icon downwards.

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.

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

In Excel, the TEXT function is used to transform integers into text. It essentially converts a numeric number to a text string.

In this method, we used the TEXT function to add leading zeros in Excel.

• Now, insert the following formula on cell D5.

• After that, drag the Fill Handle icon to cell D11.

7. Excel RIGHT Function to Add Leading Zeros

The RIGHT function returns a specific number of characters from the right side or the end of a given reference. Here, we will show how the RIGHT function is used to add leading zeros in Excel.

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

• Then, pull the Fill Handle icon.

Formula Explanation

• “0000”&C5

Here, 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

Similar Readings

• Excel VBA Codes to Bold Text in a String

8. Combination of REPT & LEN Functions to Add Leading Zeros

The REPT function is used to repeat a text or number several times in the result. And the LEN function is used to determine the number of characters in a given reference. In this section, we will use a combination of the REPT, LEN functions, and ampersand operators to add leading zeros. Here, our final result will be 5 digits for each cell.

• Insert the following formula on cell D5 and then 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

9. Excel BASE Function Adds Leading Zeros

The BASE function in Excel translates a number to its text with a specific base. With the BASE function, we can add leading zeros. Here, we are considering 10 base numbers.

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

Here, the BASE function converts C5 into a 10-base number with 5 digits.

10. Excel Power Query to Add Leading Zeros

Power Query is an Excel feature that is ideal for users who require data preparation but do not require a full array of analytic capabilities.

The Power Query Editor is the main data preparation interface, where you may connect to a variety of data sources and preview data while applying hundreds of different data transformations. We can add leading zeros with the help of the power query.

📌 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 showing here.

• We can see the Power Query window and ID column are presenting data in number form.

• First, 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.
• Now, 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 section.
• Insert the following formula in the Custom column formula section and finally press OK.

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

11. Excel VBA to Add Leading Zeros

When we need to apply any specific task multiple times, we use the VBA macro. Also, VBA code is helpful to work with large datasets. In this section, we will form a VBA code that will add leading zeros to the existing data.

📌 Steps:

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

• We entered the VBA application window.
• 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.

Read More: How to Format Text in Excel Cell

How to Remove Leading Zeros in Excel

Previously, in all the methods we have shown how to add leading zeros in Excel text format. But we want to show how to remove leading zeros in Excel. For that, we will use the VALUE function to show the magnitude of a text.

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

How to Add Leading Zeros in Excel to Make 10 Digits

In this section, we will learn how to make a 10-digit number by adding leading zeros. This 10-digit number system is widely used for mobile numbers. For other cases where you need all numbers of the same 10 digits, you can follow this process.

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

• Then, drag the Fill Handle icon.
  

Here, we have used 10 in the 3rd argument to get a 10-digit number.

Frequently Asked Questions

1. 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. After that, leading zeros will be visible.

2. How to add 0 in front of a phone number in Excel?

Ans: Follow any of the methods discussed in this article to add a zero in front of a phone number. If any phone number contains 10 digits. So, with leading zeros, that will be of 11-digits and you can use any of the following formulas.

Things to Remember

• Here, 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. Here, the number of leading zeros is not fixed, depending on the given reference.

Download Practice Workbook

Conclusion

In this article, we described how to add leading zeros in Excel text format using multiple methods consisting of formula, without formula, VBA code, and Power query. We also mentioned that in some methods the number of leading zeros is fixed and in others, the total number of digits is the same. We also showed how to remove leading zeros from Excel. I hope this will satisfy your needs.

Related Articles

• How to Draw a Line Through Text in Excel
• How to Overline Text in Excel
• How to Change Text Color with Formula in Excel