If you try to enter some data like 0000365 into an Excel cell, you'll probably notice that Excel will automatically remove the leading zeros from any numbers. This can really be hectic if you want those leading zeros in your data and you do not know how to make Excel keep them. There are, however, a few ways that I'm going to discuss below by which one can pad numbers with zero at the beginning.
Format as Text
There is an option to change the format of a range to text. Doing this will treat any data you enter as text values, even if they are numbers. This will cause Excel to keep any leading zeros in your numbers.
Procedure
1. Select the range of cells you want to enter leading zeros in.
2. Go to the Home tab.
3. In the Numbers section click on the Format Dropdown selection.
4. Choose Text from the format options.
- Numbers with leading zeros won't disappear now, once they are entered because they are text values instead of numbers.
Custom Format
You can add custom formatting to format numbers with leading zeros. They will only appear to have leading zeros though. The underlying data won't be changed into text with the added zeros.
1. Select the range of cells you want to add leading zeros to and open up the Format Cells dialog box.
2. Right-click and choose Format Cells.
3. Use the Ctrl + 1 keyboard shortcut.
4. Go to the Number tab.
5. Select Custom from the category options.
6. Add a new custom format in the Type input. If you want the total number of digits including any leading zeros to be 4 then add 0000 as the custom format.
7. Press the OK button.
After applying the custom format you will notice the data has not actually been changed, changes only appear in the worksheet with the leading zero formats.
Leading Apostrophe
You can force Excel to enter a number as text by using a leading apostrophe. This means you'll be able to keep those zeros in front as you're entering your data. This method is quick and easy while entering data. Just type a ' character before any numbers. This will tell Excel the data is meant to be text and not a number. When you press Enter, the leading zeros will stay visible in the worksheet. The changes will not be visible in the worksheet, but is still there and can be seen in the formula bar when the active cell cursor is on the cell
TEXT Function
It lets you apply custom formatting to any number of data already in your spreadsheet by adding a formula.
Formulae:
TEXT (Value, Format)
- Value – This is the value you want to convert to text and apply formatting to.
- Format – This is the formatting to apply.
If you wanted to add zeros to a number in cell C4 so that the total number of digits is 8, then you can use the above formula.