If you have tried adding leading zeros to a number in Excel, you know it is not as simple as it sounds. There is no direct option to add leading zeros. If you try to add a number with a leading zero in cells. Excel will ignore your efforts and just treat them as numbers.
For example, Suppose you have sales data with an employee id field, where you have to maintain the same size 3 digits. The same for mobile numbers and zip code fields.
In this post, you learn will how to add leading zeros in excel.
Add Leading Zeros – Format as Text
If you enter a number with leading zeros in any cell, Excel will remove them. If you format the cells as text, the system will treat numbers with leading zeros as text, making it simple to store them there.
Please follow the below steps to add leading zeros using Format
- Select cells C2:C6
- Go to Home
- Click the drop-down icon to expand
- Select Text
You can type 023 in cell C2. Excel does not remove the leading zeros.
Add Leading Zeros using Apostrophe
This is the straightforward method to add leading zeros inside Excel. Any number in the cell with a leading zero must be preceded by an apostrophe. The apostrophe changes the number into text while preserving the leading zeros.
- Go to cell C2
- Type EmpID with a leading zero start with an apostrophe ‘023
- Press Enter
Excel does not remove the leading zeros.
Add Leading Zeros using the TEXT function
The TEXT function transforms a cell value into text after receiving a cell reference as input. Additionally, it might provide the value of a certain format.
Syntax: TEXT ( Val, Format)
- Go to cell C2
- Type the formula =TEXT(A2, “000”), where you refer to the cell A2 and assign the format 000 to the text.
- Press Enter
You can see the emp id with a leading zero in cell C2
Conclusion
It is quick and straightforward to verify that your numbers are formatted correctly in Excel by adding leading zeros. When working with huge numbers or when a certain number of digits must be maintained in a cell, leading zeros are extremely crucial.
If you have any questions, please leave a comment below and we will get back to you as soon as possible.
