Clean & standardize addresses in Google Sheets™ with CityLookup.

Quickly convert large address lists into clean city, state, and country data—directly inside Google Sheets™.

No scripts, APIs, or manual cleanup required.

Call CityLookup over a range of address data like this:
=CITYLOOKUP(A1:A10)

And it will output the city name from each cell.

The following functions are also included:

- CITYFROMZIP(input) outputs a city name for the US zip code contained within the input

- [Beta] NEARESTCITY(lat,long) outputs the city that's closest to the inputted US lat/long coordinates

CITYLOOKUP Details
----------------------------------
CITYLOOKUP outputs a standardized city name from any text/address input.

Call CityLookup with an (optional) second argument of 1, and the output will include the complete city, state / province, and country. For example, if cell A1 contains "123 Main St Chicago IL" and you call CityLookup like this:
=CITYLOOKUP(A1, 1) 

It will output:
Chicago, IL United States

CITYFROMZIP Details
----------------------------------
For United States zip code data or US addresses where the zip code is at the end of the cell, consider using CITYFROMZIP(A1:A10). Call it over a cell or a range of cells and it will output the city name for the zip code contained within the address. CITYFROMZIP currently works with US addresses only; we plan to add support for other countries in the future.

[Beta] NEARESTCITY Details
----------------------------------
For US geolocation data (latitude and longitude), NEARESTCITY(lat,long) outputs the city that's closest to the geographical coordinates inputted. 

NEARESTCITY is in beta; it currently works with US geocoordinates only and it may have inaccuracies, though we are actively testing/improving it. In cases where the inputted geopoint lies close to the edge of a bordering city, the closer city will be returned. This means geopoints at the very edge of a city's boundary may return the adjacent city.

Uses and benefits:
* Clean CRM or customer exports
* Prepare mailing or donor lists
* Segment leads or customers by location
* Standardize vendor or partner data
* Normalize and categorize address and location data
* Extract city from address in any spreadsheet
* Analyze and dedupe real estate listings across multiple web sites
* Consolidate and standardize separate input files prior to analyzing or plotting on a map
* De-dupe addresses
* Match city data from separate web sites
* Lookup state, province, or country information for a list of cities

CityLookup does not provide geocoding; it simply finds and outputs clean city, state, and country information. CityLookup can be used as an alternative to geocode APIs for city-level data because its full output will plot on most mapping services.

CityLookup currently finds and matches the largest cities across the world, and many smaller cities in the US. If CityLookup isn't finding your city, please suggest it via the Support link at the bottom of this page.