How to Extract City and State from Excel Geography Data
Question details
The user needs to convert geographic location text into linked Geography data types in Excel, extract the city and state into separate columns, and sort the resulting records by frequency of occurrence.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Cleaning up and organizing location-based datasets by splitting geographic text and dynamically sorting the results based on frequency.
- Observed behavior
- Location data needs to be split into distinct city and state columns and ordered descending by how often each city appears in the dataset.
Ensure you have an active Microsoft 365 subscription and a stable internet connection, as the Geography data type relies on online access to Microsoft's Bing service to fetch location data.
Extract and Sort using Geography, TEXTSPLIT, and SORTBY
Use Microsoft 365's built-in Geography data type combined with dynamic array formulas to automatically extract and sort your location data.
This method leverages the linked data types to fetch accurate geographical information, followed by modern array formulas to split and sort the data seamlessly without manual filtering.
Select the column containing your location data. Navigate to the 'Data' tab on the top ribbon and click on 'Geography' in the Data Types group. A small map icon will appear next to your cells, indicating they are now linked.
In an adjacent empty column, enter the formula =TEXTSPLIT(A2.City.Name, ", ") (assuming your Geography data is in cell A2). This will automatically separate the linked city and state string into two distinct columns.
To sort the dataset by the most frequently occurring cities, use the SORTBY and COUNTIF functions. In a new target range, enter the formula =SORTBY(A2:C15, COUNTIF(A2:A15, A2:A15), -1, B2:B15, 1, C2:C15, 1). This sorts the records by frequency in descending order, then alphabetically by state and city as secondary criteria.
Alternative Extraction using Text to Columns
If you do not have access to the Geography data type, you can use the traditional Text to Columns feature to split location strings.
Use WPS Office for Easy Data Management
While the linked Geography data type is exclusive to Microsoft 365, WPS Office provides a robust, free alternative for handling large datasets. You can easily clean, split, and sort location data using built-in text features and advanced sorting tools without an expensive subscription.
- 1. Download and Install: Visit the official WPS website to download and install WPS Office for free.
- 2. Open your Spreadsheet: Launch WPS Spreadsheet and easily open your existing Excel workbook containing the location data.
- 3. Process your Data: Utilize WPS Spreadsheet's powerful Text to Columns feature or standard text extraction formulas to organize your city and state data effortlessly.
Frequently Asked Questions
Why is the Geography data type greyed out in my Excel?
The Geography data type requires a Microsoft 365 subscription, English editing language setup, and an active internet connection. Ensure you are signed into your Microsoft 365 account and connected to the internet.
Can I extract the zip code or population using the Geography data type?
Yes. Once your location data is converted to the Geography type, you can click the small 'Add Column' icon that appears next to the selected cell and choose additional fields like Zip Code, Population, or Area.
What if TEXTSPLIT is not available in my version of Excel?
If your version does not support TEXTSPLIT, you can use the Text to Columns feature found under the Data tab, or use a combination of LEFT, RIGHT, LEN, and FIND functions to manually separate the city and state text.
How does the SORTBY function work with COUNTIF in this scenario?
The COUNTIF function calculates the frequency of each city in your dataset. SORTBY then takes your entire data range and uses the generated COUNTIF frequency array as the primary sorting criteria (descending), followed by your state and city columns as secondary criteria to resolve ties.
