Nehad Ulfat
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- fx 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-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.