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 Methods to Keep Leading Zeros in Excel**

In this section, I will show you 10 quick and easy methods to keep leading zeros in Excel on the Windows operating system. This article contains detailed explanations with clear illustrations for everything. I have used the **Microsoft 365 version** here. However, you may use any other version depending on your availability. Please leave a comment if any part of this article does not work in your version.

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

- First, select all the cells
**(C5:C12)**containing zip codes. - Then, under the
**Home**ribbon, click on the dialogue box option as shown in the right-bottom corner from the**Number**group of commands. - Then, from the
**Format Cells**dialogue box, choose the**Custom** - Add
**00000**in the**Customized Type**format box. - Finally, press the
**OK**button.

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

- For this, 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.

- As a result, you will see that there will be leading zeros in front of the numbers.

**Read More: ****Excel Custom Number Format Multiple Conditions**

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

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

**📌 Steps:**

- Here. in
**Cell D5**insert the following formula:

`=TEXT(C5,"00000")`

**🔎 Formula Breakdown:**

- 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 in “**00000**” text format.

- 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 a similar format.

- Here, you will see an error warning as you have inserted numbers as text input. You have to ignore errors by clicking on the error icon and selecting the “
**Ignore Error**” option.

**Read More: ****How to Write 00 in Excel (11 Easy Ways)**

**3. Adding Apostrophes 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.

- For this, you have to add
**apostrophes**manually before each number and then add the**leading zeros.**Thus you can simply convert the numbers to**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.

**Read More: ****[Solved]: Leading Zero Not Showing in Excel (9 Possible Solutions)**

**4. Combining RIGHT Function with Ampersand Operator**

Now, this is another perfect method that returns the result using the **RIGHT function** and **Ampersand** operator (&). You can also use the **CONCATENATE function** instead of & operator.

**📌 Steps:**

- In
**Cell D5**insert the following formula:

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

- Press
**Enter**button. - Use
**Fill Handle**to fill down all other cells containing zip codes.

**🔎 Formula Breakdown:**

- 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 be input into 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 function.

**📌 Steps:**

- In
**Cell D5**insert the following formula:

`=BASE(C5,10,5)`

- Press
**Enter**. - Use
**Fill Handle**to autofill all other cells as required.

**🔎 Formula Breakdown:**

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.

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

**Similar Readings**

**How to Round Numbers in Excel Without Formula (3 smart ways)****Keep Leading Zero in Excel Date Format (9 Quick Ways)****Round Off Numbers in Excel (4 Easy Ways)****Change International Number Format in Excel (4 Examples)****How to Round to Nearest Multiple of 5 in Excel (3 Easy Ways)**

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

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 the
**Data**ribbon, click on**From Text/CSV**option. - A dialogue box will appear from where you have to import the file containing the data.
- Select the file & then Press
**Import.**

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

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

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

The use of **REPT & LEN** functions together is another fruitful method you can adopt.

**📌 Steps:**

- In
**Cell D5**, type-

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

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

**🔎 Formula Breakdown:**

**REPT**function 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) that need to be added or repeated.- Lastly,
**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 a Fixed Number of Leading Zeros Using CONCATENATE Function**

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

- For this, first, go to the top ribbon and press on the
**Developer**, then press on the**Visual Basic**option from the menu. - You can use
**ALT + F11**to open the**“Microsoft Visual Basic for Applications”**window if you don’t have the Developer tab added.

- Now, a window named
**“Microsoft Visual Basic for Applications”**will appear. Here from the top menu bar, press on the**“Insert”**And a menu will appear. From them, select the**“Module’”**option.

- Now, a new
**“Module”**window will appear. And**Paste**this VBA code into the box.

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

- Press
**F5**, and 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.

**🔎 VBA Code Explanation:**

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

**📌 Steps:**

- From the
**Insert**ribbon, select**Pivot Table**, and 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.

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

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

- Go to the
**Pivot Table Fields**again, you’ll find a new option-**f****x Zip Codes with Leading Zeros** - Mark 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.

**Related Articles**

**How to Put Parentheses for Negative Numbers in Excel****Use Short Date Number Format 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)****How to Apply Engineering Number Format in Excel (2 Easy Ways)****Excel Number Format Not Working (2 Reasons with Solutions)**

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.