Function Problems

How to Extract Cities from Inconsistently Formatted Addresses in Excel

John WilsonJohn Wilson Nov 2, 2026 870 views

Question details

The user needs to extract city names from a column of addresses that lack a standardized format, making traditional text-splitting methods unreliable.

How to Extract Cities from Inconsistent Addresses in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
Cleaning unstructured data and standardizing addresses for reporting or database management.
Observed behavior
Standard text functions like Text to Columns fail because the addresses do not follow a uniform structure (varying spaces, missing commas, or absent state info), requiring a list-based matching approach instead.
Before you start

Before attempting to extract the data, ensure you have downloaded or created a comprehensive master list of all possible cities that might appear in your dataset. Place this reference list in a single column to prepare for formula matching.

Solution 1Recommended

Use Lookup and Text-Processing Formulas with a Reference List

The most reliable method for unstructured addresses is to cross-reference your text against a known master list of cities using Excel's lookup functions.

Because inconsistent addresses cannot be easily split by delimiters like commas or spaces, referencing a master list ensures accurate extraction. This method scans the entire address string to see if any city name from your predefined list is present.

1
Create a master city list

Create a new worksheet in your workbook named 'CityList'. Paste your comprehensive reference list of cities into column A. To avoid false matches on overlapping names, sort this list by text length from longest to shortest.

2
Define a named range

Select your entire list of cities in column A, navigate to the Formulas tab, click 'Define Name', and assign the name 'Cities' to this range.

3
Apply the extraction formula

Return to your main dataset. In the empty cell next to your first address (for example, B2), enter the formula: =LOOKUP(2,1/SEARCH(Cities,A2),Cities) and press Enter.

4
Fill the formula down

Select cell B2, click and hold the small square at the bottom-right corner of the cell (the fill handle), and drag it down to apply the formula to the rest of your address column.

Use Lookup and Text-Processing Formulas with a Reference List
Handling Overlapping City Names: If your list contains overlapping city names (e.g., 'New York' and 'York'), ensure your reference list is sorted by length descending. This forces the formula to check for longer, specific names before shorter ones.
Efficient Data Processing

Easily Clean and Extract Address Data in WPS Spreadsheet

WPS Spreadsheet offers powerful data processing capabilities, including advanced array formulas and lookup functions, completely free of charge. You can extract, clean, and analyze complex address datasets with the exact same functions you use in Excel.

  1. 1. Open your data file: Launch WPS Spreadsheet and open your .xlsx or .csv file containing the unstructured address dataset.
  2. 2. Set up a reference sheet: Add a new sheet, paste your master list of cities, and create a Named Range from the Formulas tab.
  3. 3. Extract with familiar formulas: Use standard formulas like =LOOKUP(2,1/SEARCH()) right within WPS Spreadsheet to pull city names accurately and efficiently.
Fully compatible with Microsoft Excel (.xlsx, .xls, .csv) file formats.Supports advanced lookup, SEARCH, and array formulas for complex data extraction tasks.Lightweight, fast installation with a familiar interface for seamless migration.
microsoft office alternative - wps office

Frequently Asked Questions

Why can't I just use Text to Columns to extract the city?

If the addresses are inconsistently formatted (e.g., some have commas separating the city, others use spaces, and some lack state codes entirely), Text to Columns will misalign your data into the wrong columns. A reference lookup method is required for unstructured text.

How do I handle overlapping city names like 'Kansas City' and 'Kansas'?

When using a lookup formula, it is best to sort your reference list of cities by character length in descending order. This ensures the formula checks for and matches the longer, more specific city names before the shorter, general ones.

Where can I find a master list of cities for my reference sheet?

You can download public domain city lists from government census websites, national postal service databases, or demographic data hubs like World Population Review.

Can Power Query help with extracting cities from addresses?

Yes, Power Query can perform a cross-join or conditional fuzzy match against a reference table to extract matching city names from your address column. This approach is highly recommended and often more efficient than formulas for massive datasets.