The Scenario
You are a data ops analyst at a company that imports CRM records from three different sources. The address data across all three was stored in separate columns: street number in column A, street name in column B, city in column C, state in column D, ZIP in column E. You now have 300 rows of this split-out format and need geocoded coordinates for each row so the sales team can feed them into a territory-mapping tool. The tool requires latitude in column F and longitude in column G.
You have never used Mapbox structured forward geocoding — you have only used single-field geocoding before, which is not appropriate here because the address components are already parsed out.
The bad version:
- Concatenate all five address columns into a single string, then run that through a standard geocoding API.
- Discover that concatenation breaks for rows where the street number is blank or where the ZIP has a leading zero that Excel dropped.
- Try structured geocoding through the Mapbox API directly, discover the endpoint signature is different from the forward geocoding endpoint, and spend time reformatting your request payload.
- Process 300 rows one at a time because you do not have a batch script handy.
The sales team wants the coordinates by end of the day so they can finalize territory assignments.
The Easy Way: One Prompt in SheetXAI
SheetXAI is an AI agent that lives inside your Google Sheet. It reads the split address columns, sends each row to the Mapbox Structured Forward Geocoding endpoint using the correct field mapping, and writes coordinates back into columns F and G — without you having to concatenate strings or reformat request payloads.
Use Mapbox structured forward geocoding to geocode each row using street number in column A, street in column B, city in column C, state in column D, and zip in column E — write latitude into column F and longitude into column G.
What You Get
- Column F with decimal latitude for each of the 300 rows.
- Column G with decimal longitude.
- Any row where the structured input cannot be matched gets "GEOCODE FAILED" in column F with the specific reason — missing city, ambiguous address, invalid ZIP — so you can correct individual rows without auditing the whole set.
What If the Data Is Not Quite Ready
Some ZIP codes lost their leading zeros because of Excel's number formatting
Excel stripped leading zeros from ZIP codes like 01234, making them 1234 and causing geocode mismatches in New England states.
Before geocoding, check column E for any ZIP values with fewer than 5 digits and pad them with a leading zero. Then run Mapbox structured forward geocoding using street number in column A, street in column B, city in column C, state in column D, and corrected ZIP in column E. Write latitude into column F and longitude into column G.
About forty rows have the street number missing — only street name is in column A
Some CRM records stored the full street line in column B and left column A blank.
For rows where column A is blank, treat column B as the full street line (street number and name combined) and use Mapbox structured geocoding with just the street, city, state, and ZIP fields. For rows where column A has a value, geocode using all five structured fields. Write latitude and longitude into columns F and G for all rows.
You need the full formatted address written back as well as coordinates
The territory mapping tool also needs a canonical address string to display in tooltips.
Geocode all 300 rows using Mapbox structured forward geocoding (street number in column A, street in column B, city in column C, state in column D, ZIP in column E). Write latitude into column F, longitude into column G, and the Mapbox-resolved full formatted address string into column H.
Full pipeline: fix ZIP zeros, flag missing fields, structured geocode, flag low-confidence matches, and sort failures to top
First, pad any ZIP code in column E with fewer than 5 digits by adding a leading zero. Flag any row where columns A through E have more than two empty fields in column I as INCOMPLETE. For the remaining rows, run Mapbox structured forward geocoding and write latitude into column F, longitude into column G, and formatted address into column H. Flag any row where the geocode confidence is below 0.7 in column I as LOW CONFIDENCE. Sort the sheet so LOW CONFIDENCE and INCOMPLETE rows appear first, making the review queue visible immediately.
One prompt handles data prep, structured geocoding, quality flagging, and output sorting.
Try It
Get the 7-day free trial of SheetXAI and open the next CRM import that lands in your inbox — bring the split address columns and ask SheetXAI to structured-geocode them through Mapbox in one pass. If your geocoded rows need to feed into a driving-time matrix or a route optimization, those articles cover the downstream steps. The Mapbox hub lists every geographic workflow available.