Adding leading zeros (0) to zipcodes and similar character-based data in Microsoft Excel for use in MapInfo Pro

Products affected: MapInfo Pro™


When outputting a table with zipcodes to .csv format using MapInfo Pro, sometimes the leading 0's (zeros) are lost when the file is opened in Excel.

User-added image


This happens because Excel treats the zipcode data as a number format and not as text or character.  A number format cannot begin with a zero.  Therefore, the zipcode data needs to be formatted as character or text to retain the leading zeros.


UPDATED: October 2, 2018

Using Excel 2016 32-bit:
To resolve, open the file in Excel and select the column of data.  Then right-click and choose format cells:
User-added image

Then select "Custom" for the category and in the "Type" field enter 0#### and click OK:

User-added image

Here's the end result with leading 0's returned.  Make sure to save the file to retain the changes:

User-added image


Resolution 2:

On the existing spreadsheet where leading zeros are not selectable or missing. Open the file in Excel first.

Then clicked on FIle > Save as > Save as Type: Text (Tab delimited) (*.txt)

User-added image

Then close out of Excel. Open Excel and choose > Blank.
File > Open > Newly saved text file "txt" in Excel.

The wizard will pop up.
Check the box for My data has headers.
User-added image
Step 2 wizard. Make sure Tab is selected and hit Next
User-added image
Step 3.

Click the check box for "TEXT" and click finish.
User-added image
Click File Save as XLSX to the new Excel table.

Here is a link to a Microsoft Article that also explains how to retain leading zeros for use in any product: