Convert address lists, ZIP codes, postal codes, and latitude/longitude coordinates into clean city, state, and country data -- directly inside Google Sheets™. No scripts, APIs, or manual cleanup required.

CityLookup includes three functions:

1️⃣ CITYLOOKUP(input) - outputs a city name from any address or location text (global)

2️⃣ CITYFROMZIP(input) - outputs a city name for the ZIP code or postcode contained within the input (supports US, UK, Canada)

3️⃣ CITYFROMPOINT(lat, long) - reverse geocodes a lat/long coordinate to the city whose official boundary contains that point (US only)

CityLookup is free for up to 100 lookups per 24 hours. If that's insufficient, get unlimited reverse geocode to city, ZIP code to city, and address to city lookups for $19.99/month. Upgrade or cancel anytime from the CityLookup sidebar.

CITYLOOKUP Details
----------------------------------
Call CITYLOOKUP on a single cell:
=CITYLOOKUP(A1)

If cell A1 contains "123 Main St Chicago IL" it will output:
Chicago

Call it over a range to process multiple rows at once:
=CITYLOOKUP(A1:A10)

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

CITYLOOKUP works on any address format and finds and matches cities worldwide, with strong coverage of smaller cities in the US.

CITYFROMZIP Details (US, Canada, UK)
---------------------------------------------------------------
Call CITYFROMZIP on a single cell:
=CITYFROMZIP(A1)

If cell A1 contains "90210" it will output:
Beverly Hills

Call it over a range to process multiple rows at once:
=CITYFROMZIP(A1:A10)

CITYFROMZIP works with bare ZIP codes or full address strings.

CITYFROMZIP supports three countries:
(1) US (default): =CITYFROMZIP(A1:A10)
(2) UK postcodes: =CITYFROMZIP(A1:A10, ,"UK")
(3) Canadian postal codes: =CITYFROMZIP(A1:A10, ,"CA")

Pass an optional second argument of 1 to return city and state or province together:
=CITYFROMZIP(A1, 1) returns "Chicago, IL"
=CITYFROMZIP(A1, 1, "CA") returns "Toronto, ON"

For UK postcodes, CITYFROMZIP returns the Royal Mail post town. For Canadian postal codes, CITYFROMZIP returns the nearest service city; accuracy may vary for rural areas. For US ZIP codes that span multiple cities, CITYFROMZIP returns the largest city covered by that ZIP code.


CITYFROMPOINT Details (US)
-------------------------------------------------
CITYFROMPOINT reverse geocodes latitude and longitude coordinates to city within Google Sheets -- matching the geopoint against the city whose official municipal boundary actually contains it.
 
Call CITYFROMPOINT on a single cell:
=CITYFROMPOINT(lat, long)
 
If A1 contains 41.8781 and B1 contains -87.6298, it will output:
Chicago
 
Call it over a range of latitudes and longitudes to process multiple rows at once:
=CITYFROMPOINT(A1:A10, B1:B10)
 
Pass an optional third argument of 1 to return city and state together:
=CITYFROMPOINT(A1, B1, 1) returns "Chicago, IL"
 
CITYFROMPOINT only returns a result when the point falls within a covered city's actual boundary; if a point falls outside every covered city (for example, a rural or unincorporated area), it returns nothing rather than guessing. For those cases, CityLookup also includes NEARESTCITY, a companion function that always returns the closest city even when a point falls just outside its boundary -- see the sidebar documentation for details.
 
CITYFROMPOINT currently works with US coordinates only; city coverage may expand in the future.
 
Uses and benefits:
* Clean CRM or customer exports
* Prepare mailing or donor lists
* Segment sales leads or customers by location or lat/long data
* Parse or split addresses into separate columns by city, state, zip code or postcode
* Standardize vendor or partner data
* Normalize and categorize address and location data
* Extract city from address in any spreadsheet
* Reverse geocode GPS or latitude/longitude data to city
* 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
* Turn raw location coordinates from mobile apps, IoT devices, or GPS logs into readable city names
 
CityLookup finds and outputs clean city, state, and country information from addresses, postal codes, and coordinates -- including reverse geocoding lat/long data to city -- so its output plots cleanly on most mapping services without needing a separate geocoding API.
 
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.