Mailing lists often cram City, State, and ZIP into one cell. A few text functions split them cleanly into their own columns — no Text-to-Columns wizard needed.
FIND locates the comma; LEFT keeps everything to its left.
The example
A single cell holds 'City, ST 12345' and you want three columns.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Address | City | State | ZIP |
| 2 | Dallas, TX 75201 | Dallas | TX | 75201 |
| 3 | Austin, TX 78701 | Austin | TX | 78701 |
The formula
Three formulas, one for each part:
How it works
The pattern 'City, ST ZIP' is predictable, so position math works:
- City:
LEFT(A2,FIND(",",A2)-1)keeps everything before the comma. - State:
MID(A2,FIND(",",A2)+2,2)skips the comma and space, then takes two letters. - ZIP:
RIGHT(A2,5)grabs the last five characters. - Each formula reads the same source cell, so all three columns fill together.
If your data has 9-digit ZIPs, change RIGHT(A2,5) to TEXTAFTER(A2," ",-1) to grab the last chunk.
Try it: interactive demo
Type a 'City, ST ZIP' address — watch the three parts split out.
Variations
ZIP with TEXTAFTER
Grab whatever follows the last space, so 5- and 9-digit ZIPs both work.
Trim stray spaces first
Wrap each result in TRIM if the source has inconsistent spacing.
Pitfalls & errors
This assumes the 'City, ST ZIP' layout. Addresses with no comma or extra commas need a different rule.
A missing comma makes FIND return #VALUE!. Wrap formulas in IFERROR if your list is messy.
Practice workbook
Frequently asked questions
Can't I just use Text to Columns?
What about street addresses on a separate line?
How do I handle two-word cities?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class