Preserving Excel Leading Zeros

Excel is a very powerful program, but sometimes it doesn’t act the way we wish. A case in point is importing a CSV file with zip codes. If you’re not careful how you open your file and apply different formats, you could be in for some surprises. Excel may drop your leading zeros. The same problem can happen with part or order numbers.

Many companies have database systems that have contact data or sales data. It’s not uncommon to extract records and import them into another system. One common way of doing that is with CSV (comma separated values) files and Microsoft Excel.

Visualizing the Problem

Here’s a scenario a friend encountered. She opened a CSV file from one database into Excel where she needed to do some cleanup work. If she were to look at her original file, she would see the zip codes had the leading zeros.

I’ve opened a sample file in Microsoft’s Visual Studio Code editor. All looks good.

Pin

However, once the file was opened in Microsoft Excel, the leading zeros disappeared. You don’t see them even if you highlight the cell and look in the formula bar.

Pin

In my example file, all the zip codes had leading zeros so it’s easy to see the problem. However, if you have a large file that is not sorted on this column, it’s easy to miss.

Once my friend was done editing in Excel, she saved the CSV file and imported it to another system. Unfortunately, saving the file dropped the zeros as you can see in my code editor.

The reason this happened is Excel formatted these cells as “General”. General format cells have no specific number format. And by default, Excel omits leading zeros.

Pin

The problem was compounded because the system she imported to doesn’t do field validation to find the issue. The end result was various contact records had zip codes that were less than 5 characters.

As with many problems, there are multiple solutions and I’ll provide 3 based on whether your final files need to be .XLSX or .CSV

Solution 1: Change Cell Format and Save File to .XLSX

This solution is the easiest if you plan to change the file format from CSV to .XLSX.

1. Open the problem file.
2. Click the letter above your Zip Code column (e.g. “E”.) This will select the whole column.
3. Press Ctrl + 1. The Format Cells dialog opens.
4. Click Special.
5. Click either Zip Code or Zip Code + 4.
Pin
6. Click OK. Your zip codes will now show the leading zeros.
Pin
7. Save your file as .XLSX.

warning If you save the file in the same CSV format, the leading zeros would again drop even though it looks correct in Excel. The next time you open the CSV file, your problem is back.

Solution 2: Changing to Text Format

Sometimes you need to keep the original CSV format. Your database system may allow you to import CSV files, but not Excel files. If that’s the case, please follow these steps.

1. Open Excel first.
2. Click Open from the left menu.
3. Navigate to your .CSV file
4. Open the problem file. The Text Import Wizard – Step 1 of 3 should appear.
5. Click the Next > button.
6. On Step 2 of 3, select Comma as your Delimiter.
7. Click the Next > button.
8. On Step 3, click your column with the zip codes.
9. At the top of the dialog, change the Column Data Format to Text.
Pin
10. Click the Finish button.

Your file will open Excel. The difference is that you have a green marker in the top left corner. This marker indicates the cell is using the text format. The leading zeros have been preserved.

Pin
11. Click the Disk icon in your Quick Access toolbar to save the file.

Solution 3: The CSV File Dropped the Zeros

It could be that you saved the file from Excel and your CSV file no longer has the leading zeros. No worries, we can fix this problem.

1. Add a new column to your spreadsheet called “Fixed Zip Code”. (e.g. Column H)
2. Click in the first cell underneath that new column header. (e.g. H2)
3. Type =text(E2, “00000”) and press Enter.

info The formula above will take the contents of cell E2, our bad zip code, and convert it to a text format. And it will prepend with as many zeros needed to get to 5 digits.

Pin
4. Copy the text formula by dragging the fill handle down the column.
5. Click the Disk icon to save your file.
6. Open Excel again.
7. Navigate to the same file.
8. On Step 3 of the Text Wizard, click the original Zip Code column and choose “Do not import column (skip)“.
9. Click the corrected zip code column and change the Column data format to Text.
Pin
10. Click the Finish button

Key Takeways

• Open your CSV files within Microsoft Excel so that the Text Import Widget opens.
• Change the Column Data Format to “text”.
• Use the =text formula to create new values based on bad ones.

Practice Files

Below are two CSV files you can use to practice the solutions if you like. The first one contains full zip codes whereas the second has the leading zeros removed. All the names are fictitious and were created with a random name generator.

• attach_file CSV file with full zip codes
• attach_fileCSV file with leading zeros dropped

Related Excel Tutorials

• Customizing the Excel Toolbar
• How to Use Excel Solver
• How to Freeze Columns and Rows

Disclaimer: Images from Amazon Product Advertising API. This site may receive an affiliate commission on these products. Updated: 2020-10-19