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. theThe From Text/CSV icon OR
   2. the 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 the 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 drop-down 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 drop-down which will pull your the data back into the excel Excel spreadsheet
7. If the data changes at any point, Excel can automatically update your the data and apply transformations to the spreadsheet, to preform . To perform 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 dropdowndrop-down
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 that requires 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 ctrlCtrl+1 (hold down the ctrl 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 pre-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 Click OKbutton
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

...