The Scenario
You're a logistics analyst. A colleague who left the company six weeks ago maintained the master delivery zone workbook — 200 zip codes, column A, nothing else. The downstream systems that calculate route distances and weather enrichment all require lat/lon coordinates, but this workbook has never had them. Your first week on the job, someone asks you to run a weather analysis on all 200 zones. You need coordinates first.
The bad version:
- You find a geocoding API, realize you need to set up credentials, and discover OpenWeather has a geocoding endpoint that converts zip codes to coordinates — but you have to call it once per zip code, and the response structure varies between zip-code lookups and city-name lookups.
- You write a quick script in Python, realize you don't have the requests library installed, install it, run the script, hit a rate limit at row 37, add a sleep, re-run from the beginning, and eventually get 180 results — 20 zip codes returned ambiguous or unrecognized results that you now have to investigate manually.
- An hour and a half later, the analyst who asked for the weather data has already followed up twice.
The coordinates were supposed to be a five-minute prerequisite, not the main event.
The Easy Way: One Prompt in SheetXAI
SheetXAI is an AI agent that lives inside your Excel workbook. It reads the zip codes from column A, calls the OpenWeather Geocoding API for each one, and writes the latitude, longitude, and any metadata back into the columns you specify — flagging any that return ambiguous or unrecognized results.
For each zip code in column A, use the OpenWeather geocoding API to look up the latitude and longitude and write them into columns B and C — flag any unrecognized zip codes in column D
What You Get
- Column B: latitude (decimal degrees) for each zip code
- Column C: longitude (decimal degrees) for each zip code
- Column D: "UNRECOGNIZED" for any zip code that returned no result, so you can investigate the source data
- Once complete, the workbook is ready to feed directly into any weather, routing, or mapping API that requires coordinates
What If the Data Is Not Quite Ready
The zip codes include Canadian postal codes mixed in with US zip codes, which use different formats
For each value in column A, determine whether it's a US zip code or a Canadian postal code, call the OpenWeather geocoding API with the appropriate format for each, and write latitude, longitude, and country code into columns B, C, and D — flag any that fail to resolve in column E
You have city names instead of zip codes in some rows, and you want both resolved in the same pass
For each row in column A — treating values with 5 digits as zip codes and all others as city names — call the OpenWeather geocoding API and write latitude, longitude, and country code into columns B, C, and D — flag ambiguous results in column E
The workbook has a mix of raw addresses and zip codes, and you need just the zip extracted first
Extract the 5-digit zip code from each address string in column A and write it into column B, then use the OpenWeather geocoding API to look up lat/lon for each zip and write the coordinates into columns C and D
Geocode all 200 zip codes, write coordinates, flag errors, and verify the output is downstream-ready in one pass
For each zip code in column A, call the OpenWeather geocoding API to look up latitude and longitude, write results into columns B and C and country code into column D, flag unrecognized zip codes as 'LOOKUP FAILED' in column E, then add a note in column F confirming coordinate precision is at least 4 decimal places for all resolved rows
One prompt geocodes, validates, and checks precision — the workbook is ready for the weather or routing pipeline without a second pass.
Try It
Open the delivery zone workbook and Get the 7-day free trial of SheetXAI — ask it to resolve all 200 zip codes to coordinates so you can run your weather or routing analysis immediately. You can also see how SheetXAI handles current weather fetching for city lists once coordinates are in place, or browse the OpenWeather API hub for the full range of supported lookups.