The Scenario
You are a data analyst at a direct-to-consumer brand. A growth team web scrape produced 500 freeform address strings sitting in an Excel workbook — Import Sheet tab, column B: lines like "1234 Main St, Springfield IL 62701" and "apt 4b 901 oak avenue san francisco ca 94105."
The fulfillment team needs clean, structured fields — street, city, state, ZIP, country — in columns C through G before they can create any shipments. The fulfillment run is Wednesday. It is Monday morning.
The bad version of the next few hours:
- Build a formula to split on commas
- Watch it break on the apartment numbers
- Manually fix 80 rows where the regex failed
- Realize the state abbreviations are inconsistent
- Still on row 150 at 5 PM.
The fast version is one prompt.
The Easy Way: One Prompt in SheetXAI
SheetXAI is an AI agent inside your Excel workbook that reads the freeform address strings and calls ShipEngine's address parser for you, so structured fields land in the workbook without any formula work.
Open the SheetXAI sidebar and type:
Read the freeform address text in column B of my Import Sheet and parse each one with ShipEngine, writing the structured address fields into columns C through G: street, city, state, ZIP, country. For any row where ShipEngine cannot parse the string, write 'PARSE ERROR' into column C and leave the rest blank.
SheetXAI reads column B, sends each string to ShipEngine's address parsing endpoint, and writes the structured components back. Five hundred rows, no formulas.
What You Get
Columns C through G populated per row:
- Column C — street address, normalized and capitalized
- Column D — city
- Column E — state as a two-letter abbreviation
- Column F — ZIP code, zero-padded
- Column G — country code
Parse errors are flagged in column C so you know which rows need a human to look at the source string. The failure rate on a ShipEngine parse is usually low — most address strings that are legible to a human will parse correctly.
What If the Data Is Not Quite Ready
Web form scrapes have specific, consistent failure patterns. SheetXAI handles them in the same prompt.
When strings have extra label text mixed in
The scrape pulled "Shipping address: 1234 Main St, Chicago IL 60601 — preferred" with labels and notes mixed into the string.
For each value in column B of the Import Sheet, strip any leading labels like "Shipping address:" or "Address:" and any trailing notes after the ZIP. Then parse the cleaned string with ShipEngine and write street, city, state, ZIP, and country into columns C through G.
When international addresses are mixed in with domestic ones
About 60 rows are Canadian addresses with provinces and postal codes, not US-style ZIP codes.
Parse each address string in column B using ShipEngine. For rows where the country is Canada, write the province into column E and the postal code into column F. Write the country code into column G for all rows so I can filter by country later.
When you want to skip rows already processed from a previous run
You partially ran this last week. Columns C through G are already filled for the first 200 rows.
Skip any rows in the Import Sheet tab where column C is already filled in. Parse the remaining strings in column B with ShipEngine and write structured fields into columns C through G.
When you need to parse, validate, and produce a go/no-go flag for the fulfillment team in one pass
The fulfillment team wants a single column they can filter on.
For each address string in column B of the Import Sheet tab, parse it with ShipEngine and write street, city, state, ZIP, and country into columns C through G. Then validate each parsed address with ShipEngine and write 'valid', 'invalid', or 'corrected' into column H. For rows where ShipEngine could not parse the original string at all, write 'PARSE FAILED' into column H.
The pattern: parse and validate in one pass so the fulfillment team gets a decision-ready list, not a halfway-done one.
Try It
Get the 7-day free trial of SheetXAI and open any workbook with a column of raw address text, then ask it to parse the strings with ShipEngine. The ShipEngine integration is included in every SheetXAI plan. For related workflows, see how to validate parsed addresses before label creation or the ShipEngine in Excel overview.