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
- Open Excel
- Select the Data tab from the menu
- The From Text/CSV icon OR
- The Get Data icon
- Select From File from the Get Data dropdown and then select From Text/CVS
- The Import Data window will open. Select the file to import and click Open.
- Excel will load and display the data in a preview window. Select the maximize window button to see the options.
- If any of the columns need to be converted, select the Transform Data button (select Load if no conversions need to be made).
- Select the column header of the column that needs to be converted
- Select the Transform tab from the menu
- Select the Data Type drop-down from the toolbar
- Select the data format (i.e., Text) the column needs to be transformed to
- Select Replace current in the popup window to transform the column
- When you have completed converting the columns select the Home tab on the menu
- Select the Close & Load icon
- Select the Close & Load from the drop-down which will pull the data back into the Excel spreadsheet
- If the data changes at any point, Excel can automatically update the data and apply transformations to the spreadsheet. To perform this function:
- Select the Data tab on the menu
- Select the Refresh All button
- Select Refresh All or Refresh (to select a specific column) in the drop-down
- Select the File tab on the menu and select Save As or Save
Format Existing Excel File to include Leading Zeros
- Open the Excel workbook that requires formatting
- Select the cell or range of cells to format
- Formatting will work for number codes that contain fewer than 16 digits
- Press Ctrl+1 (hold down the Ctrl button and the number 1 button at the same time) to open the Format Cells window
- Select the Number tab
- Select the Category to format
- Select the Special category for pre-formatted options (i.e., Zip Code, Zip Code + 4, Phone Number, Social Security Number)
- Select the Custom category to format based on what is needed
- Click OK
- The formatting will now be applied for all future entries on the spreadsheet
- Please note: This process will not restore any leading zeros that were removed prior to formatting