Excel
Leading zeros in ID/serial numbers
Excel will always remove any leading zeros from anything stored as a number. You will notice that when you type 00002, it will automatically change it to 2 in the cell. Here are several ways to display ID or serial numbers that contain leading zeros.
1. One or two numbers that need leading zeros.
Unless you know you will have many numbers with leading zeros in your spreadsheet, this is a quick way to save a number as text, which will include the leading zeros.
In this example, we want to display "00002" in the cell.
Normally, when you type "00002", the zeroes will disappear.
To force it to display the zeros, type ’ (apostrophe) before the number.
This number is now stored as text which will be indicated by the small green triangle, and the leading zeros stay visible.
2. Preset your cells to Text.
If you know you will be typing an entire column or row of mixed letters and numbers, like serial numbers, you can pre-set the entire column or row to Text.
Select the entire column, row, or range of cells.
In the Home ribbon, in the Number options, pop up General and change it to Text.
Now everything will be entered as text but remember that anything that will reference those cells as numbers might complain to you because they are not stored as numbers.
3. Keep your values as numbers without converting to text.
If you know you will always have a fixed number of digits, this method may have some advantages because the numbers will stay numbers and will not be converted to text. This way, these cells can safely be referenced from numeric formulas elsewhere in your spreadsheet.
If you have an entire column that you know will contain numbers with four digits exactly, including leading zeros, you can use a custom number format.
Select the entire column.
In the Home ribbon, expand the Number options to view the Format Cells window.
Click Custom.
Type 0000 in the Type field.
The numbers will now display with the leading zeros, but you can see in the edit bar that the number is still stored as the number '2'.
