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.

**Read More:** **How to Keep Leading Zeros in Excel CSV (4 Easy Ways)**

#### **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.

**Read More:** **How to Add Leading Zeros in Excel Text Format (10 Ways)**

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

**Read More:** **How to Add Leading Zeros in Excel by CONCATENATE Operation**

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

**Similar Readings**

**How to Put Parentheses for Negative Numbers in Excel****Format a Number in Thousands K and Millions M in Excel (4 Ways)****How to Apply Number Format in Millions with Comma in Excel (5 Ways)****Custom Number Format: Millions with One Decimal in Excel (6 Ways)**

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

**Read More:** **Excel Convert Number to Text with Leading Zeros: 10 Effective Ways**

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

**Read More:** **How to Put 0 in Excel in Front of Numbers (5 Handy Methods)**

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

**Read More:** **How to Concatenate Numbers with Leading Zeros in Excel (6 Methods)**

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

**Read More:** **How to Remove Leading Zeros in Excel (7 Easy Ways + VBA)**

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

**Read More:** **How to Add Leading Zeros to Make 10 Digits in Excel (10 Ways)**

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

Unfortunately none of these methods works for me.

I have a list of article numbers. They come in lots of different ways (different lengths, only numbers, only letters, numbers+letters, some with an a – or a . or a / somewhere,… So there is no predefined special format applicable for all references.

Some are preceded by a 0 (or more 0s).

I need to use vertical lookup function to link tables.

When I copy a cell or the column, the leading 0 is still showed in the copied cell(s).

But with every action I do in excel, the leading 0 disappears. Even when I just make the cell format as “text”, the 0 still disappears. So I don’t want to add a 0, I just want to keep those that are there.

The list is also to long to add a ‘ in front of each number.

Note: When I click on a cell with a leading 0 visible in the cell, the formula bar doesn’t show that leading 0.

How to resolve?

Hi Amelie,

Thanks for your comment. As your dataset doesn’t follow any regular pattern, so I think we need an additional column to solve this issue. The procedure is:

1)First, insert a new column just right after your data column.2)Then, at the first cell of that column, write down‘[Value].Here, the [Value] represents the data of the previous column.

3)Now,double-clickon theFill Handleicon.4)The same result will be pasted on every cell. Click on theAuto Fill Options > Flash Filloption.5)Theapostrophewill add to every cell value, and it will prevent the disappearance of zero (0) from your dataset.I hope you will be able to restore your cell values accurately. Please inform us if you are still facing any trouble.