Adding leading zeros (0) to zipcodes and similar character-based data in Microsoft Excel for use in MapInfo Pro
Products affected: MapInfo Pro™
Issue
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.
Cause
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.
Resolution
UPDATED: October 2, 2018Using Excel 2016 32-bit:
To resolve, open the file in Excel and select the column of data. Then right-click and choose format cells:
Then select "Custom" for the category and in the "Type" field enter 0#### and click OK:
Here's the end result with leading 0's returned. Make sure to save the file to retain the changes:
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)
.
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.
Step 2 wizard. Make sure Tab is selected and hit Next
Step 3.
Click the check box for "TEXT" and click finish.
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:
