I need to regularly process data that includes a 9-digit custom identifier: #########. For whatever reason, the developers of this format decided that any number there can be zero. So 000000000 is a valid identifier.
However, by the time the dataset gets to me, Excel has long ago stripped any leading zeros. So an identifier that is actually “000001234” is in the dataset I receive as “1234”. If left like this, that breaks the next step in the process.
Fortunately, the Format Cells feature in Excel makes it easy to restore the leading zeros.
- Select the cell or range of cells where Excel has removed the leading zeros.
- Press Ctrl+1 to load the Format Cells option
- Select the Number tab, and in the Category list, click Custom.
- In the Type box, enter the number format: 000000000
- Click OK
The leading zeros will now be present in any cells where Excel previously removed them.