Text
Parse a full address into Street, City, State, and ZIP code
Split comma-delimited US addresses ("742 Evergreen Ter, New Haven, CT 06510") cleanly even when street or city names contain multiple words.
Anchor on commas from the right so Suite/Apt commas stay in Street
Addresses like "500 Market St, Suite 200, San Francisco, CA 94105" have two or three commas, multi-word cities ("San Francisco"), and leading-zero ZIP codes ("06510").
Anchoring from the RIGHT with negative instance numbers handles optional Suite commas effortlessly: State+ZIP sits after the LAST comma TEXTAFTER(A2, ",", -1); City sits between the 2nd-last and last comma TEXTBEFORE(TEXTAFTER(A2, ",", -2), ","); Street is everything before the 2nd-last comma TEXTBEFORE(A2, ",", -2).
| FullAddress (A) | Street | City | State | ZIP (Text) |
|---|---|---|---|---|
| 742 Evergreen Ter, New Haven, CT 06510 | 742 Evergreen Ter | New Haven | CT | 06510 |
| 500 Market St, Ste 200, San Jose, CA 95113 | 500 Market St, Ste 200 | San Jose | CA | 95113 |
Suite-safe Street, City, State, and ZIP formulas
Keep ZIP codes as text (do NOT wrap in VALUE) so New England 0-prefixed ZIPs like "06510" never lose their leading zero!
=TRIM(TEXTBEFORE(TEXTAFTER(A2, ",", -2), ","))Extract 2-letter State and ZIP from final segment
After TRIM(TEXTAFTER(A2, ",", -1)) yields "CT 06510", TEXTBEFORE(..., " ") pulls "CT" and TEXTAFTER(..., " ") pulls "06510".
=TEXTAFTER(TRIM(TEXTAFTER(A2, ",", -1)), " ")Inputs and interpretation
This parser requires the final two comma-separated segments to be City and State ZIP, with a space separating state and ZIP. It is not address validation or a general international address parser. Country suffixes, missing delimiters, commas inside a city, or quoted CSV fields need different handling. Keep ZIP and ZIP+4 as text.
Try the example
Use the original values or change them to test your understanding.