Adding Leading Zeros in Excel
IF you have tried adding leading zeros to your cell value, you might have noticed Excel ignores them once the editing of the cell has finished. Using a few tricks, you can use to ensure that the leading zeros are kept intact. To do so, just carry out the following steps:
- One of the easiest ways to add leading zeros is by prefixing the value with a single apostrophe ('). This converts the numeric formatting to text formatting.
- If any error is signaled, click on the flag and select the 'Ignore Error' option from the pop-up list.
NOTE: The values in the cell can be used for computation or calculation purposes. If your value needs to be exactly five digits in length, then you can use this alternative instead. Carry out the steps as listed under:
- Select the cell to be formatted and go to the 'Home' tab.
- Click on the small arrow appearing near the 'Numbers' group.
- The 'Format Cells' dialog box appears with the 'Number' tab in focus.
- Select the 'Special' option from the Category' panel of the dialog box.
- Select the 'Zip Code' option from the 'Type' section of the dialog box.
- Click 'OK'.
The number automatically appears as a 5-digit number with the required leading zeros. If you require the number to be longer than 5 digits, you can use this alternative instead. Carry out the steps as mentioned below:
- Select the cell to be formatted and go to the 'Home' tab.
- Click on the small arrow appearing near the 'Numbers' group.
- The 'Format Cells' dialog box appears with the 'Number' tab in focus.
- Select the 'Custom' option from the 'Category' panel of the dialog box.
- Type in the required number of zeros in the 'Type' textbox of the dialog box to reach to the desired number of digits.
- Click 'OK'. The number automatically appears as a number with the required leading zeros.
0 comments