Right now I split Street/City/State/ZIP with Data > Split text to columns, then Flash Fill the abbreviations, but 1k rows still takes me about 18 minutes; Text Blaze helps for recurring city/state pairs. Is there a quicker route — maybe a formula combo or a paste routine — to standardize and validate in one pass?
, 18 minutes for 1k rows is rough. Text Blaze is great for repeats, but if your rows are consistently ‘Street, City, ST ZIP’, use one ARRAYFORMULA that REGEXEXTRACTs the four parts and VLOOKUPs State to a 50-row map so USPS abbreviations and ZIPs get standardized in one pass; if formats vary a lot, regex gets messy. I lean on the Pub 28 list for the map: | Postal Explorer — are your inputs mostly in that comma format or do you have unit lines mixed in?
I switched to a tiny Apps Script that batches rows through the Smarty US Street API (https://www.smarty.com/products/us-street-api) and writes back standardized Street/City/ST/ZIP+4 plus DPV; 1k rows finishes in about 30s. If APIs are off-limits, a quick Named function wrapper around your REGEX method is the next best — are your inputs always ‘Street, City, ST ZIP’ or do unit lines creep in?
Try a pre-normalize pass: one ARRAYFORMULA with REGEXREPLACE to fix punctuation/double spaces and standardize directionals, then SPLIT and validate the state via VLOOKUP against a 50-state list plus a ZIP regex — it chews through 1k rows in seconds vs. Flash Fill. If you need postal-grade validation, @ozimmerman’s API route is the inspection after the car wash.
I’d make the ZIP the source of truth and let one formula normalize off that — 18 min per thousand is . Parse the line once, then VLOOKUP the ZIP against a ZIP→city + state table and map street types/directionals via a tiny ref sheet built from USPS Pub 28 (Publication 28 - Postal Addressing Standards | Postal Explorer); mismatches get flagged so you can filter and fix in one pass. Want a sample sheet with the lookup tabs prefilled?
What sped me up was turning the parse/standardize into one Named function and running MAP over the column — regex cleanup, split, uppercase, then XLOOKUP the ZIP to a reference to return canonical city/ST and flag disagreements; @sparrow nudged me toward the validation step, and Pub 28 abbreviations are here: Publication 28 - Postal Addressing Standards | Postal Explorer. You’ll need a ZIP→city+ST table on a second tab; want a sample sheet?