The Scenario
You run marketing ops for a company that hosts monthly webinars. The registration form has a free-text location field — no dropdowns, no validation. You are staring at this month's sheet: 400 rows of raw location strings. "SF Bay Area." "NYC." "London UK." "Greater Manchester." "APAC." "Remote." Your segmentation logic for the follow-up campaign requires clean city, region, and country columns. The campaign goes out Monday.
The bad version:
- Write a lookup formula that tries to map each raw string to a standard location using a reference table you maintain by hand
- Find that the reference table covers maybe 60% of the variations — the other 40% throw errors or map wrong
- Hand-fix the remaining 160 rows one at a time while your colleague pings you asking if the segments are ready
The location field was supposed to be a nice-to-have for personalization. It became a two-day manual project the moment the registrations started coming in freeform.
The Easy Way: One Prompt in SheetXAI
SheetXAI is an AI agent that lives inside your Google Sheet. It reads the raw location strings in the sheet and uses PDL's location cleaner to parse each one into structured city, region, and country fields.
For each raw location string in column A, clean it with PDL and write the structured city, region, and country into columns B, C, and D.
What You Get
- Column B populated with the resolved city name, standardized to PDL's canonical form
- Column C populated with the region or state
- Column D populated with the country code or name
- Rows where PDL cannot confidently parse the string — like "Remote" or "APAC" — flagged in column E so you can decide how to handle them in your segmentation
What If the Data Is Not Quite Ready
The location strings include company names mixed in with locations
For each entry in column A, check if it looks like a location string or a company name. If it appears to be a location, clean it via PDL and write city, region, and country to columns B, C, and D. Flag entries that look like company names or other non-location text in column E for manual review.
You want to add a timezone column for scheduling send times
Clean each location string in column A via PDL and write city, region, and country to columns B, C, and D. Then use the resolved city to add a UTC offset to column E for campaign scheduling.
Some rows have both city and country, some have just country, some are regions
Normalize each location in column A via PDL regardless of how specific or vague it is. Write whatever level of detail PDL can resolve — city, region, or country — into the corresponding columns B, C, and D, and note the precision level in column E.
Clean locations, add hemisphere, and flag EU registrants for GDPR routing in one prompt
Clean each location string in column A via PDL and write city, region, and country to columns B, C, and D. Flag any row where the country resolves to an EU member state in column E as "EU — GDPR routing" for the compliance handoff.
The pattern is to ask for normalization and compliance routing in a single prompt — you walk away with a campaign-ready sheet that already knows which rows need legal review.
Try It
Get the 7-day free trial of SheetXAI and open any Google Sheet with a column of free-text location entries from a form or 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.