INTRODUCTION
EXCEL provides us with a vast variety of options to do almost whatever we wish to do concerned with our data analysis. And if, by chance, something is not available, we have the tools with the help of which , we can do that easily.
Similarly a very simple but challenging situation occurs when we need some leading zeroes before a number in EXCEL.
In this article we’ll discuss how to sort out this problem easy and fast.We’ll discuss different ways to apply leading zero to Text as well as Number.
ADD LEADING ZERO TO TEXT IN EXCEL
There is nothing to be done if we need to add zero to the text.
Simply, add the zero anywhere in the field and it would sustain. If somehow there is any problem, follow the following steps.
- Select the cell containing text.
- Right Click the text and choose FORMAT CELLS.
- Choose TEXT format for the cell.
- We can also choose TEXT FORMAT from the HOME TAB directly.
LEARN BASICS OF NUMBER FORMATTING HERE.
ADD LEADING ZERO TO ANY NUMBER IN EXCEL
Adding leading zero to a number is the tricky one for many of us.
Suppose we have enter the bank account number. some bank account numbers have leading zero
or
We want to create a series and want to have that all the number have same number of significant digits.
In both of the cases we are in trouble.
WHERE IS THE PROBLEM??
The Excel removes all the leading zero from the numbers which we type in Excel. So it becomes a problem for us.
SOLUTION
We have many solutions for this problem. We’ll discuss the main two of them. But the solution will depend on the
nature of the requirement.
SOLUTION 1:IF THE NUMBER NEED NOT TO DO ANY MATHEMATICAL TASK
If the number, which needs the leading zero, need not to be involved in any type of mathematical task, which means
that we are not going to apply any formula on that number, we can simply change the format of the cell to TEXT.
STEPS:
Select the cell containing text.
- Right Click the text and choose FORMAT CELLS.
- Choose TEXT format for the cell.
- We can also choose TEXT FORMAT from the HOME TAB directly.
SOLUTION 2: IF THE NUMBER NEEDS TO BEHAVE LIKE A NUMBER ONLY
A situation may arise when we need the leading zero and still number needs to be a part of the function or formula, it needs to behave like a number only.The SOLUTION 1 won’t work for this as if we convert the format to TEXT, it won’t be able to participate in a function or formula.Here we’ll make use of the custom format.CUSTOM FORMAT IS THE FINAL DESTINATION FOR ALL FORMAT RELATED PROBLEMS.
STEPS
- Right click the cell (which needs the format change) and click format cells.
- The format dialog box will open.
- Go to the bottom and choose CUSTOM.
- Click on GENERAL and delete it and enter 000… and look on the top of the dialog box, where sample is shown.
- Stop when sample shows the format you want.
- Click ok