How to Extract City and State From Full Address in Batch in Excel
Address strings are often stored in one crowded column even when teams need clean location fields for routing, reporting, or territory assignment. BatchGPT helps extract city and state from those rows using one repeatable prompt inside Excel.
Why Address Parsing Fits a Spreadsheet Workflow
The task is repetitive, but the address formats are not. That makes a prompt-driven Excel workflow useful because the output can stay structured while reviewers keep the original address visible beside it.
How to Use the BatchGPT Excel Add-in for This Workflow
- Write the prompt that tells the add-in what to do with each selected cell value.
- Select the Excel cells or range you want to process. For larger datasets, work in clean batches of rows.
- Choose the output column and adjust optional settings such as reasoning effort or web search when the task really needs them.
- Click Generate so the add-in processes each selected cell separately and writes the result to the output column you chose.
- Review the results in Excel, refine the prompt if needed, and rerun only the rows that need another pass.
Prompt Example for City and State Extraction
Ask for a strict schema and clear null behavior so messy rows do not produce misleading results.
Extract city and state from this address string:
Return labeled output with:
- city
- state
- postal_code
- review_flag
Rules:
- Use null when a field is missing
- Do not invent a city or state
- Set review_flag to yes when the address is ambiguousSample input row:
A2: 500 W Madison St, Suite 1200, Chicago, IL 60661Sample output row:
city: Chicago
state: IL
postal_code: 60661
review_flag: noHow to Keep Location Parsing Practical and Safe
Address cleanup gets more useful when you design the prompt for exceptions, not just the easy rows.
- Include postal_code or country if your routing workflow depends on them, even if city and state are the main target fields.
- Return a review flag for rows with PO boxes, missing separators, or incomplete address fragments.
- Work through imports from different systems in separate runs when the formatting patterns differ a lot.
- Keep the original address string untouched so staff can validate questionable outputs quickly.
FAQ
Can I extract city and state from mixed address formats?
Yes, but the prompt works best when you define a strict schema and a review path for ambiguous rows.
Can I include postal code too?
Yes. Add postal_code or any other location field you need to the requested output schema.
Can this support routing or territory assignment?
Yes. Clean location fields are often useful for sales territory rules, service routing, and regional reporting.
Should I trust every parsed row automatically?
No. Address data can be messy, so it is better to review rows flagged as ambiguous before using them in downstream workflows.
Turn Full Address Strings Into Cleaner Location Fields
BatchGPT is useful when the same parsing logic applies across many address rows in Excel. Set the schema once, process the selected cells, and review city and state outputs beside the original addresses.
Get started!