Keep Leading Zeros in Excel

by
PinterestLinkedInRedditPocket

Microsoft Excel is a powerful program, but sometimes it doesn’t act the way we wish. A case in point is importing a comma-separated values (CSV) file with zip codes. If you’re not careful how you open your file, you could be in for some surprises. For example, excel may drop your leading zeros. The same problem can happen with part numbers or order numbers. Below are 3 ways to keep leading zeros in Excel.

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

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 data cleanup work. If she were to look at her original file, she would see the postal codes had the leading zeros.

I’ve opened a sample file in Microsoft’s Visual Studio Code editor. All looks good and you can see the leading zeros.

CSV source file with zeros intactPin
Postal codes show in text editor

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.

Example of dropped zero in zip codePin
Zip code column and formula bar show 820

In my example file, all the zip codes had leading zeros, making it easy to see the problem. However, if you have a large unsorted file, 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.

Source file with leading zeros dropped.Pin
Leading 0’s dropped

The problem was compounded because the CMS (content management system) she imported to doesn’t do field validation to find the issue. The 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 from the Category column.
  5. Click either Zip Code or Zip Code + 4.
Formatting cells with Special and Zip Code.Pin
Changing Cell Format to Zip Code
  1. Click OK. Your zip codes will now show the leading zeros.
Excel shows full zip code.Pin
Leading zeros reappear with proper cell format
  1. Save your file as .XLSX.

⚠ If you save the file in the same CSV format, the leading zeros would again drop even though it looks correct in Excel. So 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 Microsft 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.
Import Wizard changing column data format to text.Pin
Note the leading zeros show in Zip Code column
  1. Click the Finish button.

Your file will open in 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.

Excel cells with green market indicating text format.Pin
Note green marker in top left corner
  1. 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. We can fix this problem with the TEXT function..

  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.

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

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

Key Takeaways

  • 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.

Download CSV (Full zip codes)
Download CSV (Leading 0’s Dropped)
PinterestLinkedInRedditPocket