Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Keep Leading Zeros in Excel (10 Suitable Methods)

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.

keep leading zeros with number formatting in Excel

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

Number Custom formatting in Excel

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

keep leading zeros with number formatting in Excel


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.

Using Built-in Special Formats to Apply ZIP Code format

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

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.

Applying Text format to add leading zeros in Excel

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

ignoring error for numbers in text format

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

Using Text Function to Add Preceding Zeros

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

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.

Adding Apostrophes before Numbers to keep leading zeros in Excel

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

Combining RIGHT Function with Ampersand Operator to Keep Leading Zeros

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

Using BASE Function to keep leading zeros in Excel

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

Similar Readings


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.

importing data to Excel from txt or csv file

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

transform data in Excel power query

  • Now, the Power Query window will appear.
  • Under Add Column ribbon, select Custom Column.

adding custom column in Excel power query

  • Add the column name under the New Column Name option.
  • Now under the Custom Column Formula, type-

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

  • Press OK.

using Text.PadStart function in Excel power query

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

  • Select Close & Load option.

loading power query data in Excel worksheet

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.

output of power query in Excel

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.

Using REPT and LEN function

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

Using CONCATENATE function

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.

Microsoft Visual Basic for Applications

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

insert module

  • 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

VBA code of keeping leading zeros in Excel

  • 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 output to keep leading zeros in Excel

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

inserting pivot table in Excel

  • In the Table/Range box, select the whole Table Array (B4:C12).
  • Mark the option ‘Add this data to the Data Model’.
  • Press OK.

selecting data range for pivot table

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.

Adding measure in pivot table

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

Using CONCATENATEX function in measure formula of pivot table

  • Go to the Pivot Table Fields again, you’ll find a new option- fx Zip Codes with Leading Zeros
  • Mark this option & you’ll see the result on the left in the spreadsheet.

Mark the new measure in pivot table to Keep Leading Zeros in Excel

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

Nehad Ulfat

Nehad Ulfat

Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

2 Comments
  1. 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-click on the Fill Handle icon.
      4) The same result will be pasted on every cell. Click on the Auto Fill Options > Flash Fill option.
      5) The apostrophe will 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.

Leave a reply

ExcelDemy
Logo