The Scenario
You run marketing ops. The webinar registration form your team set up eight months ago had a free-text location field. Nobody added validation. You are looking at the month's registrant export in Excel: 400 rows with entries like "SF Bay Area," "Greater London," "NYC," "DACH region," and "WFH." Your follow-up campaign requires clean city, region, and country columns for geo-personalization and GDPR routing. The campaign sends Monday.
The bad version:
- Write a lookup table with 50 common location strings and build a VLOOKUP formula to normalize the column
- Watch the formula return wrong results for anything abbreviated, regional, or in a non-English format — about 200 of the 400 rows
- Manually fix the remaining 200 by hand, running into ambiguities like "Metro Area" or "Remote" that have no clean city answer and leaving them blank
You built the form. You did not expect to spend Friday cleaning up what the form collected.
The Easy Way: One Prompt in SheetXAI
SheetXAI is an AI agent that lives inside your Excel workbook. It reads the raw location column and uses PDL's location cleaner to parse each entry into structured city, region, and country fields.
Normalize every location string in my Excel Registrants table Location column using People Data Labs and fill in the City, Region, and Country columns.
What You Get
- The City column populated with the resolved city name in PDL's standard form
- The Region column populated with the state, province, or administrative region
- The Country column populated with the country name or ISO code
- Entries that PDL cannot parse — like "Remote" or "APAC" — flagged in a Notes column so you can decide how to handle them in the campaign segmentation
What If the Data Is Not Quite Ready
Some entries are company office names rather than locations
For each entry in the Location column, attempt PDL location normalization. If the result does not look like a location — for instance if it matches a company name or returns no structured components — flag it in the Notes column as "Non-location — review" and leave City, Region, and Country blank.
You want to add a timezone offset column for send-time optimization
Normalize each location string via PDL and populate City, Region, and Country. Then use the resolved city to add a UTC Offset column for the campaign scheduling tool.
The campaign platform requires ISO 3166 country codes, not full country names
Normalize each location via PDL and write the city and region to the corresponding columns. Write the ISO 3166-1 alpha-2 country code to the Country column instead of the full name.
Normalize locations, flag EU registrants, and add GDPR routing in one pass
Normalize each location string via PDL and populate City, Region, and Country. Flag any row where the country resolves to an EU member state in a GDPR column as "EU — consent required" for the legal team's routing.
One prompt cleans the location data and adds the compliance flag — the campaign goes out Monday with the right personalization and the right routing, without a separate legal review step.
Try It
Get the 7-day free trial of SheetXAI and open any Excel workbook with a column of free-text location entries from a form or event registration. Ask it to normalize each one using PDL and write structured city, region, and country back. Then see how to normalize job titles with PDL seniority enrichment or go back to the People Data Labs overview.