Keep ZIP and ZIP+4 codes in CSV to Excel

Protect five-digit ZIP leading zeros and hyphenated ZIP+4 add-ons from numeric conversion.

ZIP and ZIP+4 codes need every digit

United States ZIP codes can be five digits (02108) or ZIP+4 (02108-4501). Leading zeros are common in the Northeast and elsewhere. Excel’s general format turns 02108 into 2108. A hyphenated ZIP+4 can survive as text, become a broken split across columns when the delimiter is wrong, or look date-like when a shorter token such as 03-04 sits in a nearby column. The teaching file keeps both ZIP forms beside a city label.

location_id,zip5,zip_plus4,city
L-14,02108,02108-4501,Boston
L-27,00501,00501-0001,Holtsville
L-03,10001,10001-0002,New York

These places are real city names paired with well-known example ZIP shapes for teaching. They are not a customer list. Compare 02108 and 00501 after every handoff; if the leading zero is gone, address validation and shipping tools will disagree with the source.

Load the same bytes from /samples/zip-plus-4.csv, or choose “ZIP and ZIP+4 codes” in the tool sample pack. Nothing is uploaded. After Preview, the local preflight summary flags leading zeros in zip5 and ZIP+4-shaped values in zip_plus4 so you can leave those columns on Text.

Before and after: Excel mess versus text workbook

A casual General-format open often shows:

location_id  zip5   zip_plus4    city
L-14         2108   02108-4501   Boston
L-27         501    00501-0001   Holtsville
L-03         10001  10001-0002   New York

Five-digit ZIPs lost their zeros. ZIP+4 may still look intact in that screenshot, which is why people miss the injury: the five-digit column is already a number. A text-preserving XLSX from CSV to Excel keeps the source characters:

location_id  zip5    zip_plus4     city
L-14         02108   02108-4501    Boston
L-27         00501   00501-0001    Holtsville
L-03         10001   10001-0002    New York

Check the formula bar, not a padded display format. A custom ZIP format that prints five digits on a numeric 2108 is still 2108. Mailing software that expects 02108 will reject or misroute it.

Convert without letting Excel guess

1. Keep the original mailing or ERP export. Do not Save As CSV from a workbook that already opened the file as General.
2. Open CSV to Excel, choose Comma, keep the header, and preview. You can load the ZIP sample pack entry instead of your private file while rehearsing.
3. Confirm zip5 still shows five characters including zeros, and zip_plus4 still contains the hyphen and four-digit add-on.
4. Leave both ZIP columns on Text. Optionally set an unrelated quantity column to Number; ZIP columns must stay Text.
5. Download the XLSX (this tool names it with a -converted suffix so it does not replace the original CSV) and spot-check the formula bar on 02108 and 00501.

Keep ZIP codes as text →

Step-by-step troubleshooting

1. Preview already shows 2108 or 501. The CSV lost zeros earlier. Local padding cannot invent them. Re-export from the source — see Repair versus re-export.
2. ZIP+4 split into two columns. The file may be comma-delimited while a hyphenated field was not quoted, or a semicolon file was opened as comma. Fix the delimiter with CSV check & repair first (one-column guide).
3. Number on zip5 blocks download. That is intentional. 02108 and 00501 match the leading-zero guard. 10001 would be accepted as a number in the same column, which is why a mixed ZIP field cannot be Number “just for New York.” Switch the column back to Text.
4. Number on zip_plus4 blocks download. Values that start with zero, including 02108-4501 and 00501-0001, fail as leading zeros. 10001-0002 fails as not a plain decimal because of the hyphen. The tool does not interpret ZIP+4 as a date serial or as two numbers. Keep Text.
5. A nearby column became a date. Tokens such as 03-04 or 12-3 are a different injury. See date-looking text. ZIP+4 with five digits, a hyphen and four digits is a different pattern; still keep it as Text.

What TableMender actually does with this sample

On CSV to Excel, every column defaults to Text. The numeric export checker rejects leading zeros and hyphens; it does not call a ZIP API. After Preview, preflight reports leading zeros in column 2 and ZIP+4-shaped values in column 3. That report is heuristic: a nine-digit ZIP without a hyphen (021084501) is a long digit token, not a ZIP+4 match, and still needs Text. Canadian patterns such as M5V 2T6 are letters and spaces; they also need Text, even though they will not match the US ZIP+4 heuristic.

Honest limits

Already-truncated codes such as 2108 cannot gain a leading zero from formatting or from this converter. ZIP+4 add-ons that were dropped in an earlier Save As stay dropped. The tool does not geocode cities, check digit counts against a region, or rewrite ZIP+4 into a nine-digit barcode. Size limits (5 MiB, 20,000 records) are capacity limits, not proof that a ZIP is safe as a number.

Edge cases and boundary cases

Postal codes outside the US may mix letters and digits (for example Canadian and UK patterns). Those still need Text. A column that mixes five-digit and ZIP+4 values should stay Text for every row; do not mark Number “just for the New York rows.”

If Save As CSV already dropped zeros, prefer a fresh export — see Excel Save As pitfalls. Leading zeros in product codes are the same numeric habit: Keep leading zeros. Matching tool: CSV to Excel.

FAQ grounded in the converter

Will a ZIP format in Excel restore 02108? A display format can print five digits on the number 2108. The stored value is still 2108. Shipping APIs that compare strings will not match.

Should I store ZIP as a number to sort? Numeric sort of 501, 2108 and 10001 is not postal order, and it already destroyed zeros. Sort the text column, or keep a separate numeric key that is not the ZIP.

What about ZIP+4 without a hyphen? Nine digits such as 021084501 hit Excel’s identifier problem the same way long IDs do. Keep Text. See long digit IDs if the token exceeds fifteen digits.

Number export on zip-plus-4.csv

Load zip-plus-4.csv in CSV to Excel with Comma and a header. The preview has 3 data records and 4 columns. The header is source record 1. L-14 is source record 2. Set one column to Number, read the list, then set it back to Text. Download stays blocked while any issue remains.

A custom ZIP format in Excel can print five digits on the number 2108. The stored value is still 2108. Compare the formula bar with the CSV. Two-decimal display is for amounts you explicitly mark as Number; it does not restore a dropped zero.

What preflight counts on zip-plus-4.csv

After Preview, with every column still on Text, the identifier row warns: leading zeros in column 2 (2 cells) and ZIP+4-shaped values in column 3 (3 cells). 10001 is five digits without a leading zero, so it is not in the leading-zero list. All three ZIP+4 values match the five-digit-hyphen-four-digit heuristic. The how-to link on that row is this page. Link order is scientific notation, then 16+ digit tokens, then ZIP+4, then plus-prefixed phones, then leading zeros. This file has no exponent and no 16-digit token, so ZIP+4 wins the link. The warning text still names the five-digit zeros.

The date-looking row stays clear. 02108-4501 is five digits, a hyphen and four digits; the date heuristic looks for short slash or hyphen windows such as 03/04/2026 or 12-3, not ZIP+4. The formula-like row stays clear. There is no fully blank data record and no exact whole-row duplicate. Empty-record removal removes 0 rows. A clear duplicate check does not make 02108 safe as an Excel number.

A nine-digit ZIP without a hyphen (021084501) is a long digit token, not a ZIP+4 match, and still needs Text. Canadian patterns such as M5V 2T6 are letters and spaces; they also need Text, even though they will not match the US ZIP+4 heuristic. Matching tools: CSV to Excel, CSV check & repair.

← All guides