Page History

...

• When creating files for importing data, Excel automatically formats certain data in a number format which may remove leading zeros if they are present. This allows Excel to utilize mathematical operations in the spreadsheet.

• This article includes how to convert Excel files to include leading zeros as well as how to format existing files in Excel to keep leading zeros by using the custom format or special format feature.

• When creating Excel import files, it may be important for the data to include leading zeros if they are present in the original data.

Convert Leading Zeros when Opening Excel File

1. Open Excel
2. Select the Data tab from the menu
3. Select
   1. the From Text/CSV icon OR
   2. the Get Data icon
      1. Select From File from the Get Data dropdown and then select From Text/CVS
4. The Import Data window will open up. Select the file to import and click the Open button
5. Excel will load and display the data in a preview window. Select the maximize window button to see options
6. If any of the columns need to be converted, select the Transform Data button (select Load if no conversions need to be made)
   1. Select the column header of the column that needs to be converted
   2. Select the Transform tab from the menu
   3. Select the Data Type dropdown from the toolbar
   4. Select the data format (i.e. Text) the column needs to be transformed to
   5. Select Replace current in the popup window to transform the column
   6. When you have completed converting the columns select the Home tab on the menu
   7. Select the Close & Load icon
   8. Select the Close & Load from the dropdown, this will pull your data back into the excel spreadsheet
7. If the data changes at any point, Excel can automatically update your data and apply transformations to the spreadsheet, to preform this function
   1. Select the Data tab on the menu
   2. Select the Refresh All button
   3. Select Refresh All or Refresh ( to select a specific column) in the dropdown
8. Select the File tab on the menu and select Save As or Save

Format Existing Excel File to include Leading Zeros

1. Open the Excel workbook needing formatting
2. Select the cell or range of cells to format
   1. Formatting will work for number codes that contain fewer than 16 digits
3. Press ctrl+1 (hold down the ctrl button and the number 1 button at the same time) to open the Format Cells window
4. Select the Number tab
5. Select the Category to format
   1. Select the Special category for per formatted options (i.e. Zip Code, Zip Code + 4, Phone Number, Social Security Number)
   2. Select the Custom category to format based on what is needed
6. Select the OK button
7. The formatting will now be applied for all future entries on the spreadsheet
   1. Please note: This process will not restore any leading zeros that were removed prior to formatting

Excel automatically removes leading zeros in order to allow formulas and math operations to work on them. This tip sheet provides instruction on how to keep leading zeros on Excel text files. Click to download the tip sheet. 

...

Related articles

...