Shipping exports, CRM dumps, and event signups all hand you the address as one blob. Sorting by state or grouping by ZIP means breaking it into components first.
Addresses look structured. They are not. They are a convention that everyone follows slightly differently.
The Old Way (SPLIT plus REGEXEXTRACT)
The obvious move is splitting on the commas, since a well-formed address has three of them.
=SPLIT(A2, ",")"742 Evergreen Terrace, Springfield, OR 97477" lands in three tidy columns. Then a row reads "742 Evergreen Terrace, Apt 4B, Springfield, OR 97477" and produces four, so city is now sitting in the state column and every row below it is misaligned.
The usual patch is pulling the ZIP separately with a pattern, then working backwards.
=IFERROR(REGEXEXTRACT(A2, "\d{5}(?:-\d{4})?$"), "")That holds until a row ends with a country name, or omits the ZIP, or writes the state as "Oregon" instead of OR. Each variation needs another branch, and the formula grows a nested IF for every format your sources use.
The FITS Way (Just Ask)
With FITS, you name the component you want and let the model handle the punctuation.
=FITS("Return only the city from this address, nothing else: " & A2)Copy that across four columns and change one word in each. Street, city, state, ZIP. An apartment line does not shift anything, because nothing is counting commas.
=FITS("Return only the 2-letter state abbreviation for this address. If the state is written in full, convert it: " & A2)That second formula does something no SPLIT can. It normalizes while it parses, so "Oregon" and "OR" both come back as OR and your pivot table groups them together.
For the ZIP, you can state the edge case in the sentence instead of a regex quantifier.
=FITS("Return only the 5-digit ZIP code from this address, dropping any +4 extension. Blank if there is none: " & A2)When to Use Each
If every address came from one validated form with a fixed shape, SPLIT is free and instant. Reach for =FITS() when addresses arrive from several sources, when apartment and suite lines appear on some rows, or when you need the state normalized as part of the parse. Related jobs use the same approach: splitting text by delimiter for simpler fields, extracting numbers from text when you want the digits only, and normalizing data once the columns exist. For the full picture, read the guide to automating Google Sheets tasks you used to need regex for.
Stop Counting Commas
FITS puts plain-English AI formulas inside Google Sheets. Name the component you want. Get the same columns on every row, apartment number or not. Free tier included.