• Skip to main content
  • Skip to secondary menu
Productivity Portfolio logo

Productivity Portfolio

Valuing your time & technology

  • HOME
  • Tutorials
    • Excel
    • Google
    • Word
    • Outlook
    • Terms
  • Quick Tips
    • Email
    • Security
    • Web
    • Windows
    • Internet of Things
    • Cell
  • Reviews
    • Health
    • Services
    • Software
    • Hardware
  • About
    • About Me
    • Contact Page
    • Privacy Policy
  • Newsletter
  • Resources
    • Practical Gift Ideas
    • Books
    • Favorite Podcasts
    • Online Courses
    • Online Services
  • SEARCH
You are here: Home › Tutorials › Excel › Preserving Excel Leading Zeros

Preserving Excel Leading Zeros

LAST UPDATED: November 28, 2020 By Anne Hennegar

TwitterPinterestLinkedInRedditFacebookPocketEmail

Microsoft Excel is a 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.

Article Contents

  • Visualizing the Problem
  • Solution 1: Change Cell Format and Save File to .XLSX
  • Solution 2: Changing to Text Format
  • Solution 3: The CSV File Dropped the Zeros
  • Key Takeaways
  • Practice 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 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.

CSV source file with zeros intactPin

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

In my example file, all the zip codes had leading zeros, making it 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.

Source file with leading zeros dropped.Pin

The problem was compounded because 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.
  5. Click either Zip Code or Zip Code + 4.
  6. Formatting cells with Special and Zip Code.Pin
  7. Click OK. Your zip codes will now show the leading zeros.
  8. Excel shows full zip code.Pin
  9. 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.
  10. Import Wizard changing column data format to text.Pin
  11. Click the Finish button.
  12. 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
  13. 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.
  4. 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.

    Cell with Excel text formula.Pin
  5. Copy the text formula by dragging the fill handle down the column.
  6. Click the Disk icon to save your file.
  7. Open Excel again.
  8. Navigate to the same file.
  9. On Step 3 of the Text Wizard, click the original Zip Code column and choose “Do not import column (skip).“
  10. Click the corrected zip code column and change the Column data format to Text.
  11. Text Import Wizard and skipping column from import.Pin
  12. 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.

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

Related Excel Tutorials

  • How to Remove #div/0 In Excel
  • How to Use Excel Solver
  • How to Freeze Columns and Rows
Excel Formulas & Functions For Dummies
Bluttman, Ken (Author); English (Publication Language); 400 Pages - 11/06/2018 (Publication Date) - For Dummies (Publisher)
$20.19

Disclaimer: Images from Amazon Product Advertising API. I may receive an affiliate commission on these products if you buy. Updated: 2021-01-14

TwitterPinterestLinkedInRedditFacebookPocketEmail

Filed Under: Excel, Tutorials Tagged With: Excel Formulas & Functions

© 2021 · Productivity Portfolio | PO BOX 117361 Burlingame, CA 94011

I use cookies to ensure that I give you the best experience on our website. If you continue to use this site I'll figure that you are happy with that. You can find more details in the privacy policy.OKNoPrivacy policy