Tag: Microsoft Excel

Using Format Cells in Excel to Restore Missing Leading Zeros

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.

  1. Select the cell or range of cells where Excel has removed the leading zeros.
  2. Press Ctrl+1 to load the Format Cells option
  3. Select the Number tab, and in the Category list, click Custom.
  4. In the Type box, enter the number format: 000000000
  5. Click OK

The leading zeros will now be present in any cells where Excel previously removed them.