How to Extract Cities from Inconsistently Formatted Addresses in Excel
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.

- 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 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.
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.
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.
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.
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.
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.

Extract Cities Using a Custom VBA Macro
A VBA macro automates the comparison process and writes the matches as plain text, reducing formula calculation lag on very large datasets.
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. Open your data file: Launch WPS Spreadsheet and open your .xlsx or .csv file containing the unstructured address dataset.
- 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. Extract with familiar formulas: Use standard formulas like =LOOKUP(2,1/SEARCH()) right within WPS Spreadsheet to pull city names accurately and efficiently.

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.




