In Microsoft Excel, it’s not possible to keep leading zeros by simply typing a number with 0’s at first as Excel, by default, will remove those zeros & keep only later parts. I want to illustrate in this article how we can add or keep those leading zeros with a number of easy & fruitful functions & techniques.

**Download Practice Workbook**

You can download the practice book that we’ve used to prepare this article. You can change or modify the cells & see the results at once with embedded formulas.

**10 Simple Methods to Keep or Add Leading Zeros in Excel**

**1. ****Formatting Cells to Keep Leading Zeros**

To keep leading zeros in Excel, formatting cells provides the simplest option to follow. You can customize the number or convert it into text format with a few clicks only.

**1.1 Customizing Number Format**

Here’s the list of 8 states with their **Zip Codes**. Now we want to keep the same format for all zip codes by adding 0’s before them where necessary.

**Steps:**

➤ Select all the cells** (C5 : C12)** containing zip codes. ➤ Under the Home ribbon, click on the dialogue box option as shown in the right-bottom corner from the **Number** group of commands.

➤ From **Format Cells **dialogue box, choose the **Custom** option.

➤ Add 00000 in the** Customized Type** format box.

➤ Press **OK**.

So, here now you’re seeing all the zip codes with the same format by keeping leading 0’s where necessary.

**1.2 Using Built-in Special Formats**

We can do the same thing by choosing the Special option in the **Format Cells** dialogue box. Then select the **Zip Code** option, press **OK** & you’re done.

You can go for other common formats here available like- **Phone Number, Serial Security Number** etc. These are all assigned to the default location USA. You can also change the location by selecting another area or country from the** Locale** drop-down & then you’ll be able to find similar options as number formats for the selected region.

#### **1.3 Applying Text Format**

By using** Text** format, we can also keep the leading zeros as required.

**Steps:**

➤ Select the cells where you want to input zip codes.

➤ From the **Number** group of commands, Select the format as** Text **from the drop-down.

➤ Now start typing all the zip codes in the cells & you’ll see no leading zero is removed.

But you’ll find an error message saying “**Number Stored as Text**” from a yellow drop-down option beside the cells. Select **‘Ignore Error**’ for each cell & you’re done.

As this method takes some time to execute for all data manually, so this method is effective when you need to input data rather than formatting a range of stored data.

**2. ****Using Text Function to Add Preceding Zeros**

**TEXT** function is another fruitful way you can use to format the numbers by adding leading zeros.

**Steps:**

➤ In **Cell D5**, type-

`=TEXT(C5,"00000")`

➤ Press **Enter** & you’ll see the desired result.

➤ Now to do the same for all cells, use the **Fill Handle** option from **Cell D5** to fill down to the last **Cell D12** & you’ll find all the zip codes with similar format.

**🔎 What’s exactly happening here?**

**⇒** The **TEXT** function converts a number to text in a specific value format.

**⇒ **Here inside the parenthesis, **C5** is the cell value that is formatted by the “**00000**” text format.

**3. ****Adding Apostrophe before Numbers**

We can also use an apostrophe before a number & then add zeros as required. This will convert the cell into a text format.

As this method represents the text format, so you’ll find the error message again containing ‘**Number Stored as Text**’. So you’ll have to select Ignore Error for all the cells having error messages.

**4. ****Combining RIGHT & CONCATENATE Functions**

Now this is another perfect method which returns the result by combining two functions- **RIGHT & CONCATENATE**.

**Steps:**

➤ In **Cell D5**, type-

`=RIGHT("00000"&C5,5)`

➤ Press **Enter**

➤ Use **Fill Handle** to fill down all other cells containing zip codes.

**🔎 Short Explanation of the Functions:**

**⇒** Here, the **RIGHT** function returns the specified number of characters from the end of a text string.

**⇒ **We’re concatenating **00000** before each number by using** Ampersand(&) **between the 00000 & the cells.

**⇒ **Now, the **RIGHT** function is allowing only the last 5 characters of each resultant data to input in the cells.

**5. ****Using BASE Function**

**BASE** function is used to convert a number to different number systems** (Binary, Decimal, Hexadecimal or Octal)**. You can also define how many characters you want to see through this fucntion.

**Steps:**

➤ In **Cell D5**, type-

`=BASE(C5,10,5)`

➤ Press **Enter**.

➤ Use **Fill Handle** to autofill all other cells as required.

**🔎 Short Explanation of the Function:**

Inside the arguments of the **BASE** function,

**⇒ C5** is chosen as the **Cell **value for **C5**.

**⇒ 10** is the radix or number system for decimal values.

**⇒ **And** 5** is the number of characters we want to see as a result.

**6. ****Importing Text File & Then Adding Leading Zeros by Using Power Query Option & PadText Functions**

You can also import a range of data & then convert them into a fixed format by adding leading zeros. If you’re using **Microsoft Excel 2010** or higher version then you’ll be able to use this method with **Power Query Editor**.

**Steps:**

➤ Under **Data** ribbon, click on **From Text/CSV**

A dialogue box will appear from where you have to import the file containing the data.

➤ Select the file & then Press **Import.**

**Step 2:**

➤ A new dialogue box will appear with the imported data & you have to click on **Transform Data**.

**Step 3:**

➤ Now, the **Power Query **window will appear.

➤ Under **Add Column** ribbon, select **Custom Column**.

➤ Add the column name under the **New Column Name** option.

➤ Now under the **Custom Column Formula**, type-

`=Text.PadStart([Column1],5,"0")`

➤ Press **OK**.

You’ll find the new column with formatted zip codes.

**Step 4:**

➤ Select** Close & Load** option.

Now, a new worksheet will appear & the newly obtained data from the Power Query Editor will be transformed here with the pivot table as shown below.

**7. ****Merging REPT & LEN Functions Together**

Use of **REPT & LEN** functions together is another fruitful method you can adapt.

**Steps:**

➤ In **Cell D5**, type-

`=REPT(0,5-LEN(C5))&C5`

➤ Press **Enter** & you’ll be displayed the formatted values right away.

**🔎 Short Explanation of the Functions:**

**⇒** **REPT** has come from Repeat word & this function repeats text a given number of times.

**⇒** **LEN** function returns the number of characters of a text string.

**⇒** Now, inside the arguments,** 0** has been added first as this is the text value we want to repeat as leading zeros before numbers as required.

**⇒ 5-LEN(C5)** denotes the number of leading zeros(0’s) needs to be added or repeated.

**⇒** Last of all, **C5 Cell** is added to the whole function by using **Ampersand(&) **as this cell value has to be placed after leading zeros.

**8. ****Adding Fixed Number of Leading Zeros**

If you want to add a particular number of leading zeros before a number or a text then **CONCATENATE** function alone will do the job for you. Here are different types of numbers in **Column B** & we’re going to add two leading zeros before each number.

**Steps:**

➤ In **Cell C5**, type-

`=CONCATENATE("00",B5)`

➤ Press **Enter**.

➤ Use **Fill Handle** to autofill the other cells up to **C12**.

So, in the picture below, you’re seeing all the numbers with two leading 0’s.

**9. ****Using VBA to Keep Leading Zeros**

We can also keep or add leading zeros by using **VBScript.**

**Steps 1:**

➤ Press **Alt+F11** to open the **VBA** window.

➤ From the **Insert** tab, select **Module** command. This will launch a new module window.

**Step 2:**

➤ Now in the module window, we have to type the codes to execute the function. Copy the codes below & paste them into your module-

```
Sub KeepLeadingZeroes()
Range("C5", Range("C5").End(xlDown)).Select Selection.NumberFormat = "000000"
End Sub
```

➤ Press **F5**, it’ll run the codes or macro.

➤ Press **Alt+F11** again to return to your Excel spreadsheet & you’ll see the result as shown in the following picture.

**🔎 What are we coding inside the VBA module?**

**⇒ **We’re adding the **Subroutine** section first & naming our macro as **KeepLeadingZeros**.

**⇒ **With the **Range **command, the 1st Cell is being selected that needs to be formatted.

**⇒ **With **End(xldown)** & **Select **sub-commands, we’re selecting the entire range of cells containing the zip codes.

**⇒ **In the next line, through the **NumberFormat** sub-command, we’re defining the number of digits(5) with 0’s.

**10. ****Keeping or Adding Leading Zeros in the Pivot Table**

In a **Pivot Table**, we can use **DAX (Data Analysis Expression) **formula to keep or add leading zeros by formatting a column or row.

**Step 1:**

➤ From the **Insert **ribbon, select **Pivot Table**, a dialogue box named **Create Pivot Table** will appear.

➤ In the **Table/Range** box, select the whole **Table Array (B4 : C12).**

➤ Mark the option ‘**Add this data to the Data Model**’.

➤ Press **OK.**

With these steps, you’re now able to convert your data into a pivot table where DAX measures are available.

**Step 2:**

➤ A new worksheet will appear & on the right, you’ll see the **Pivot Table Fields** window.

➤ **Right-click** your mouse on the** Range.**

➤ Select **Add Measure..**.

A new dialogue box named **Measure** will appear. This is called **DAX Formula Editor**.

**Step 3:**

➤ Type **Zip Codes with Leading Zeros** as **Measure Name** or anything else you prefer.

➤ Under the **Formula bar**, type-

`=CONCATENATEX(Range, FORMAT([Zip Codes], "00000”), ", ")`

➤ Press **OK.**

**Step 4:**

➤ Go to the **Pivot Table Fields** again, you’ll find a new option- **fx Zip Codes with Leading Zeros**

➤ Mark on this option & you’ll see the result on the left in the spreadsheet.

**Concluding Words**

These are the all easy & effective ways you can follow to add or keep leading zeros in Microsoft Excel data. If you know more basic & fruitful methods I should add then let me know through comments. Or you can have a glance at our other informative & interesting articles on this website.