Handling Leading Zeros in CSV Files

When you load a CSV file into Excel, it tries to determine the format of the data being loaded. Excel automatically recognizes the field as a number when importing it. By default, then, the number is displayed using one of the number fields, excluding any leading zeros.

Below is a step-by-step procedure on how to make Excel preserve leading zeros:

  1. Make sure the CSV file is renamed so it has a TXT extension (You MUST perform this step, or the rest of the steps will NOT work because Excel won't start the Text Import Wizard in Step 5).

  2. Display the Open dialog box. (In Excel 2007, click the Office button and then click Open. In Excel 2010, click the File tab of the ribbon and then click Open.)

  3. Using the Files of Type drop-down list at the bottom of the dialog box, indicate that you want to open Text Files (*.prn; *.txt; *.csv).

  4. Select the file you renamed in Step 1.

  5. Click on Open. Excel starts the Text Import Wizard, displaying the Step 1 of 3 dialog box.

  6. Make sure the Delimited choice is selected, and then click on Next. Excel displays the Step 2 of 3 dialog box.

  7. Make sure Comma is selected as a delimiter, then click on Next. Excel displays the Step 3 of 3 dialog box. The data in your TXT file should be displayed at the bottom of the dialog box, including any leading zeros in your fields.

  8. At the bottom of the dialog box, click on the field that has leading zeros. The entire column should now be selected.

  9. In the Column Data Format area, make sure the Text Radio button is selected.

  10. Repeat Step 8 and for any other fields that have leading zeros.

  11. Click on Finish. Your file is imported, with leading zeros still intact.

Now you can do your work in Excel, as desired, and again save your data in CSV format. (You will need to use Save As rather than Save.) The leading zeros will be included in the data that is saved.

Did you find it helpful? Yes No

Send feedback
Sorry we couldn't be helpful. Help us improve this article with your feedback.