Add or Keep Leading Zeros in Excel (10 Suitable Ways)

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.

Keep leading zeros by number formatting

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.

Keep leading zeros by number formatting

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

Keep leading zeros by number formatting


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.

          Keep leading zeros by special formatting


        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.

Keep leading zeros by using 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.

Keep leading zeros by using text format


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.

Keep leading zeros by using text function

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

Keep leading zeros by using apostrophe

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.

Keep leading zeros by using apostrophe


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.

Keep leading zeros by using RIGHT and CONCATENATE

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

Keep leading zeros by using BASE function

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

Keep leading zeros by using power query & padtext

Step 2:

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

Keep leading zeros by using power query & padtext

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.

Keep leading zeros by using power query & padtext

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

Step 4:

➤ Select Close & Load option.

Keep leading zeros by using power query & padtext

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.

Keep leading zeros by using power query & padtext


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.

Keep leading zeros by using REPT and LEN

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

Keep fixed or particular number of leading zeros


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.

Keep leading zeros by using VBA

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.

Keep leading zeros by using VBA

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

Keep leading zeros in pivot table

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.

Keep leading zeros in pivot table

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.

Keep leading zeros in pivot table

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.

Keep leading zeros in pivot table


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 You May Also Like

How to Remove Leading Zeros in Excel (7 Easy Ways + VBA)

How to Remove Commas in Excel [9 Smart Ways]

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!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo