📊 Spreadsheet & BI Engineering Guide · Excel / CSV / Power Query

Excel ZIP Code Lookup, Leading Zeros (01001–09999) & Heat Map Guide

Microsoft Excel automatically strips leading zeros from numeric postal codes—corrupting 3,500+ US Northeast & Puerto Rico ZIP codes (00501–08989), East German PLZ (01067), Spanish CP (08001), and Colombian 6-digit codes (050001). Fix truncated codes permanently with =TEXT(A2,"00000"), join City/State/County via XLOOKUP, and build native Excel Filled Map heat maps in 3 steps.

Global Leading-Zero Truncation Zones & Exact Excel Formula Fixes

Country & Affected Region	Broken Numeric Read	Correct Official Code	Excel Restoration Formula	Reference
🇺🇸 USA (MA, NJ, CT, ME, NH, VT, RI, PR)	`2108` / `7030`	`02108` Boston · `07030` Hoboken	`=TEXT(LEFT(TRIM(A2),5),"00000")`	US ZIPs →
🇪🇸 Spain (Provinces 01–09: Barcelona, Balearic)	`8001` / `1001`	`08001` Barcelona · `01001` Vitoria	`=TEXT(A2,"00000")`	Spain CP →
🇩🇪 Germany (Leitzone 0: Saxony, Thuringia)	`1067` / `4109`	`01067` Dresden · `04109` Leipzig	`=TEXT(A2,"00000")`	DE PLZ →
🇨🇴 Colombia (Antioquia 05 & Atlántico 08)	`50001` / `80001`	`050001` Medellín · `080001` Barranquilla	`=TEXT(A2,"000000")`	Colombia →
🇧🇷 Brazil (Greater São Paulo 01000–09999)	`1310100`	`01310-100` Av. Paulista (SP)	`=TEXT(A2,"00000-000")`	Brazil CEP →

US East Coast & Global Leading-Zero Postal Code Truncation Audit (2026): 3,642 New England/NJ/PR ZIP codes (prefixes 00–09) and 38 countries suffer 100% spreadsheet integer corruption without UTF-8 string formatting — Source: PostalCodes.info — **US East Coast & Global Leading-Zero Postal Code Truncation Audit in CSV / ERP Pipelines (2026):** Key takeaway: Unquoted CSV imports in Excel and ERP pipelines strip leading zeros from 3,642 US ZIP codes (00501–09898 across MA, NJ, CT, ME, NH, VT, PR) and 38 countries (Spain 01–09, Germany 01–09, Italy 00–09, Brazil 01–09).

Copy-Paste Excel & Google Sheets Formulas for ZIP Code Lookup & Cleaning

1. Strip ZIP+4 hyphen (e.g. 02108-1402 → 02108) and pad 5-digit leading zero:

=IF(LEN(TRIM(A2))>5, LEFT(TRIM(A2),5), TEXT(VALUE(TRIM(A2)),"00000"))

2. XLOOKUP City, State/Province & County from Postalcodes.info UTF-8 CSV:

=XLOOKUP(TEXT(A2,"00000"), PostalDB!$A:$A, PostalDB!$B:$D, "Unmatched Code", 0)

How to Create a ZIP Code Heat Map in Excel (Filled Map Choropleth)

Step 1: Format your postal code column as 5-digit text using =TEXT(A2,"00000") and name the header ZIP Code or Postal Code. Step 2: Add an explicit Country column (for example United States or Germany) next to it so Bing Maps geocoding never confuses 28001 (Madrid, Spain) with 28001 (Albemarle, NC). Step 3: Highlight the Country, Postal Code, and Metric columns, then click Insert → Maps → Filled Map (Excel 2019 / Microsoft 365).

📥 Download Clean UTF-8 CSV & XLSX Datasets (249 Countries) → 🎯 Export ZIP Codes in a Mile/KM Radius to CSV → 🌍 Handle 00000 No-ZIP Countries →

Frequently Asked Questions: Excel ZIP Codes, CSV Imports & Heat Maps

How do I keep leading zeros in ZIP codes in Excel (00501 to 09999)?

Select the column, press Ctrl+1 (Format Cells -> Special -> Zip Code or Custom -> 00000), or convert values permanently as text with =TEXT(A2,"00000"). When opening a CSV file, use Data -> From Text/CSV (Power Query) and set the postal_code column type to Text before loading so Excel never strips leading zeros in NJ, MA, CT, PR, Germany (01067), or Spain (08001).

How do I look up City, State, and County from a ZIP code in Excel?

Download a UTF-8 reference CSV from Postalcodes.info/datasets, ensure both lookup keys are formatted as 5-digit text with =TEXT(A2,"00000"), and use =XLOOKUP(TEXT(A2,"00000"), Ref!$A:$A, Ref!$B:$D, "Not Found") to return City, State, and County in one spill formula.

How do I create a ZIP code heat map in Excel?

Format your postal_code column as Text (with a header named ZIP Code or Postal Code), add a Country column so Bing Maps geocoder disambiguates identical 5-digit codes across countries, select both columns plus your metric column, and click Insert -> Maps -> Filled Map.