Excel Leading Zeros (Table of Contents)
Introduction to Leading Zeros in Excel
The concept of putting zeroes in excel is used where keeping zeroes at the starting of the numbers are required, so the format for the number system will not get changed. While working, we may see some situations where if we paste some numbers of selected columns to another file, if those numbers have zeroes at the starting then, those zeroes will get removed, as Zero has no significance at the starting of any number sequence.
This can be prevented by customizing the numbers in the Format Cells option.
How to Add Leading Zeros in Excel?
To add leading Zeros in excel is very simple and easy. Let’s understand how to add leading Zeros in excel with some examples.
Excel Leading Zeros – Example #1
- We have a sample list of some random numbers from 10 to 40, as shown below.
As we can see, all the numbers are positive in nature and do not have any Zeros at the starting. In fact, they have Zeroes at the end, which make a valuable addition.
- For adding leading Zeroes in these numbers, first, select the number and right-click on it. Now, from the right-click menu, select Format Cells, as shown below.
- After that, we will get a Format Cells box.
- Now from the Category section, go to Custom as shown below.
- Custom is the category where we can customize any defined format or create a new format as required. Here, we need to add leading Zeroes in numbers. So, as we have selected the data already with that only while selecting Custom, it has taken us to General, as shown above. Now for adding leading Zeroes for selected data, select any Zero from the list as shown below.
- Now in the Type box, edit the number of leading Zeroes as per requirement. Here we have added 3 Zeroes as the numbers which we have selected are of 2nd digit numbers. So the 3 Zeroes in this will add only one leading Zero in the selected numbers, as shown below.
- Once we added, we will see the Sample output in the same box itself just above the Type box. Once done, click on OK.
- We will see the leading Zeroes in the selected numbers after that, as shown below.
Excel Leading Zeros – Example #2
There is another method where the leading zeroes from any file or table may get removed. Suppose we face a situation where we need to import data from any notepad file. And in that file, data already has leading zeros, as shown below.
Data in the Notepad file is always in the text. So there is no impact if we keep data; there is a real format. But when we import this data to excel, the column name Product Numbers with leading Zeroes in it may get converted into number format. As zeroes at the start mean nothing, if it is a number, then it will get removed automatically. To avoid this, while importing the data from the Text file, select the Delimited option as shown below and click Next.
- Now check the Delimiters as Tab and click on Next.
- And in the third step, select complete data first as in format select Text to finish the process by pressing Shift + Ctrl + Left click. And click on Finish to complete it.
- Now another window will come up. From there, select the reference cell for importing the data and click on Ok once done.
- Once we finish the process, we will get the output of our imported data in text format, as shown below.
We can use this data anywhere, the format will be the same, and it will not eliminate leading Zeroes. If it is required to change the Product number format from text to numbers, this can be done in Format Cell under the Custom category, which we have seen in example-1.
4.9 (7,687 ratings)
View Course
Pros of Leading Zeros in Excel
- Adding leading Zeroes in number is required to make data in a uniform format.
- There might be a situation where you may get data in different number length, but to make it equal, if we add the same number of Zeroes, then the length will become equal for all the selected numbers. This formats the data in a proper manner.
Cons of Leading Zeros in Excel
- In adding leading Zeroes when we import the data, if the data gets converted into text, it will no longer be used for numbering functions.
Things to Remember
- While importing the data from any file to excel, if you convert the data into text, then later, you again need to convert that data into a number by using a custom category for adding leading Zeroes.
- In format pasting the data, leading zeroes will get removed, so avoid doing it if your selected data is already in the text. There is not any problem if we format+ paste converted numbers into text in other format or file. It will remain as it is without changing the format.
Recommended Articles
This has been a guide to Leading Zeros in Excel. Here we discuss how to Add Leading Zeros in Excel and excel examples and a downloadable excel template. You can also go through our other suggested articles –
