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

Get FREE Advanced Excel Exercises with Solutions!

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.

Overview of 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.

Dataset for how to add leading zeros in Excel 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).

Insert zeros manully in data

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

Apply Flash Fill from the ribbon

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.

Removing errors from cell

  • You can see no error is showing now.

Leading zeros manually added in dataset


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.

Insert leading zeros using apostrophe

  • 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.

Apply Flash Fill using keyboard shortcut

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.
="00"&C5
  • Then, pull the Fill Handle icon.

Adding leading zeros using ampersand

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.
=TEXTJOIN( ,TRUE,"00",C5)

Excel TEXTJOIN function to add leading zeros

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.
=CONCAT("00",C5)

Adding leading zeros in Excel using CONCAT function

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.

=CONCATENATE("00",C5)

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.
=TEXT(C5,"00000")
  • After that, drag the Fill Handle icon to cell D11.

Use of TEXT function to add leading zeros


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.
=RIGHT("0000"&C5,5)
  • Then, pull the Fill Handle icon.

RIGHT function to add leading zeros

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


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.
=REPT(0,5-LEN(C5))&C5

Combination of REPT and LEN functions to add leading zeros

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.
=BASE(C5,10,5)

Adding leading zeros using BASE function

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.

Select range to enter power query

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

Ensure the heading of table range

The selected range is also showing here.

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

Data transformed to Power Query

  • 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.

Change the data type into text

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

Allow to replace the current data

  • 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.

Add new column in Power Query

  • 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.
=Text.PadStart([ID],5,"0")

A new column using formula

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

Leading zeros added in Power Query


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.

Enter VBA from worksheet

  • 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

Paste code in VBA module

Code Breakdown

  • Dim Cell_1 As Range, Dim Total_Digits, Required_Zeros As Long

Declaring the variables with type.

  • Total_Digits = 5

Set the value of Total_Digits 5.

  • Set Cell = Selection

Select a range in the worksheet as the input of Cell.

  • For Each Cell_1 In Selection

Apply the loop for each cell of the selected range.

  • Required_Zeros = Total_Digits – Len(Cell_1.Value)

Determine the required number of leading zeros for each cell.

  • If Required_Zeros > 0 Then, Cell_1.Offset(0, 1).Value = String(Required_Zeros, β€œ0”) & Cell_1.Value

If any zero is required, add those zeros in the leading position of the existing cell values and paste them into the corresponding cell of the next column.

  • Else, Cell_1.Offset(0, 1).Value = Cell_1.Value

If no zero is required, then paste the existing value in the corresponding cell of the next column.

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

Run the VBA code

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

Leading zeros has been added by VBA

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.
=VALUE(C5)

Remove leading zeros by VALUE function


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.
=BASE(C5,10,10)
  • Then, drag the Fill Handle icon.

Create a 10-digit number by adding leading zeros

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.

=BASE(C5,10,11)

=REPT(0,11-LEN(C5))&C5


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

Sabrina Ayon
Sabrina Ayon

Hi there! This is Sabrina Ayon. I'm really excited to welcome you to my profile. Currently, I'm working in SOFTEKO as a Team Leader. I'm a graduate in BSc in Computer Science and Engineering from United International University. I love working with computers and solving problems. I’ve always been interested in research and development. Here I post articles related to Microsoft Excel. Hoped this may help you. Thank you.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo