To split an address such as “1200 Maple Grove Ln Apt 4B, Springfield, IL 62704” into four columns, count the commas from the right. In Microsoft 365 or Excel 2024, =TRIM(TEXTBEFORE(B2, ",", -2)) returns the street, =TRIM(TEXTAFTER(TEXTBEFORE(B2, ",", -1), ",", -1)) the city, =TEXTBEFORE(TRIM(TEXTAFTER(B2, ",", -1)), " ") the state and =TEXTAFTER(TRIM(TEXTAFTER(B2, ",", -1)), " ", -1) the ZIP code. That works whenever every address ends “City, ST ZIP”. When the commas are missing, the state is spelled out or a ZIP has lost its leading zero, the formulas return errors or, worse, wrong answers with no error at all.
A mail merge, a shipping tool or a CRM import wants the street, city, state and ZIP in their own columns. A list typed by many people over many years often has the whole mailing address in one cell. Here’s the formula way, what it handles, and where it stops.
Tip: which Excel you need
TEXTBEFORE, TEXTAFTER, TEXTSPLIT and HSTACK need Microsoft 365 or Excel 2024. For Excel 2021 and earlier, see the first of the common questions below.
The sample list
| A | B | |
|---|---|---|
| 1 | Name | Address |
| 2 | Dana Whitfield | 1200 Maple Grove Ln Apt 4B, Springfield, IL 62704 |
| 3 | Marcus Ortega | 2500 W Lake Ave, Austin, TX 78701-2233 |
| 4 | Priya Nandakumar | 88 Harbor View Dr, Unit 12, Portland, ME 04101 |
| 5 | Tom Kessler | 9 Birch Way, Columbus, OH 43215 |
The names and addresses are made up.
Method 1: TEXTSPLIT, one column per comma
=TRIM(TEXTSPLIT(B2, ",")) in C2, filled down, spills each address across one column for each piece between commas. It’s the formula version of Text to Columns. For Dana’s row it gives the street, Springfield, and “IL 62704”.
Row 4 shows the catch. Priya’s unit has a comma of its own, so every part after it moves one column to the right:
| C | D | E | F | |
|---|---|---|---|---|
| 2 | 1200 Maple Grove Ln Apt 4B | Springfield | IL 62704 | |
| 4 | 88 Harbor View Dr | Unit 12 | Portland | ME 04101 |
Method 2: Count the commas from the right
The end of a US address is the steady part: city, comma, state, space, ZIP. TEXTBEFORE and TEXTAFTER accept a negative instance number, which counts from the end of the text, so an extra comma near the start no longer matters.
Street (C2)
=TRIM(TEXTBEFORE(B2, ",", -2))
Everything before the second-to-last comma. Priya’s street keeps its unit: “88 Harbor View Dr, Unit 12”.
City (D2)
=TRIM(TEXTAFTER(TEXTBEFORE(B2, ",", -1), ",", -1))
The piece between the last two commas.
State (E2)
=TEXTBEFORE(TRIM(TEXTAFTER(B2, ",", -1)), " ")
The first word after the last comma.
ZIP (F2)
=TEXTAFTER(TRIM(TEXTAFTER(B2, ",", -1)), " ", -1)
The last word.
Fill all four down:
| C | D | E | F | |
|---|---|---|---|---|
| 1 | Street | City | State | ZIP |
| 2 | 1200 Maple Grove Ln Apt 4B | Springfield | IL | 62704 |
| 3 | 2500 W Lake Ave | Austin | TX | 78701-2233 |
| 4 | 88 Harbor View Dr, Unit 12 | Portland | ME | 04101 |
| 5 | 9 Birch Way | Columbus | OH | 43215 |
Tip: one formula instead of four
LET and HSTACK put all four parts in one cell that spills across C to F: =LET(a, B2, last, TRIM(TEXTAFTER(a, ",", -1)), HSTACK(TRIM(TEXTBEFORE(a, ",", -2)), TRIM(TEXTAFTER(TEXTBEFORE(a, ",", -1), ",", -1)), TEXTBEFORE(last, " "), TEXTAFTER(last, " ", -1)))
ZIP codes: five digits and the lost zero
The ZIPs in column F are text, so 04101 keeps its zero. A lost zero comes from earlier in the list’s life. A ZIP column that was ever stored as numbers shows 7022 where 07022 belongs. Text to Columns on “ME 04101”, split on the space, turns 04101 into 4101 unless you set that column to Text in step 3 of the wizard. One formula restores the zero and trims ZIP+4 to five digits:
ZIP, five digits (G2)
=TEXT(LEFT(F2, 5), "00000")
“78701-2233” becomes 78701, and “7022” becomes 07022. Keep column F if you want ZIP+4.
Warning: the CSV round trip
A CSV saved from Excel keeps the zero, but double-clicking that CSV to open it drops the zero again by default. Use Data → From Text/CSV instead, and set Data Type Detection to “Do not detect data types” so every column comes in as text.
Where formulas stop
Here is what the same four formulas return on addresses typed the way real lists are:
| Address | What the formulas return |
|---|---|
| 45 Cedar St Ste 210 Denver CO 80202 | #N/A in every column: there are no commas to count |
| 7 Elm Ct #3, Boston, Massachusetts 02108 | State “Massachusetts”, not MA |
| 500 Canyon Rd, Santa Fe, New Mexico 87501 | State “New” |
| c/o Rivera, 410 Pine St, Seattle WA 98101 | Street “c/o Rivera”, City “410 Pine St”, State “Seattle”, ZIP “98101”, and no error |
| 123 Main St | #N/A: there is no city, state or ZIP to find |
Each one has a patch: IFERROR for the errors, a lookup table of state names, a filter for “c/o”. The units stay inside the street (“Apt 4B”, “Unit 12”, “#3”), and pulling them out takes a list of every way people write one. An address with no commas can’t be fully split by position. The state and ZIP at the end can (see the last common question), but nothing marks where the street ends and the city begins, and “Ann Arbor” and “Salt Lake City” are more than one word.
The #N/A rows at least show themselves. The c/o row doesn’t. It fills three of the four columns with the wrong parts (only the ZIP is right), and on a list of a few thousand you won’t spot it until the mail comes back.
Excel Data Cleaner: when the list won’t follow one pattern
Addresses typed every which way?
The formulas above need every address to follow one pattern. A real list doesn’t: some rows have commas and some don’t, units sit inside the street, states are spelled out, and a few ZIPs lost a zero long ago.
Excel Data Cleaner reads each address, with commas or without, and splits it into Street, City, State and ZIP columns. Apt, Unit, Suite, Ste, #, Floor and similar values move to a column of their own. State names become two-letter codes, and ZIPs come out as five digits with their leading zeros, or as ZIP+4 if you prefer. A care-of line is set aside for you to check, and a street with no city is marked Incomplete. You review every result before it is applied. Free to try with up to 100 records.
See Excel Data CleanerWindows PC required: it runs inside Excel on Windows, not on a phone, a tablet or a Mac. Why it does not run on Mac.
Watch it done, 2:21. Twelve addresses typed every way, split into street, unit, city, state and ZIP in Excel Data Cleaner, with every result reviewed before it is applied. The cleaned parts come out in capital letters.
Addresses arriving every week from the same system?
Tell us about your data and we’ll build a tool that handles it.
Small fixes start at $69.
Every project gets a fixed-price quote before any work starts: the price you approve is the price you pay.
“David is among the most talented programmers I have ever worked with.”
Excel Apps Client · Upwork
Common questions
Can I split addresses in Excel 2021 or earlier?
Yes. For a one-off, work on a copy of the data, select the column, and use Data → Text to Columns → Delimited → Comma; it writes over the columns to its right. For formulas, =LEFT(B2, FIND(",", B2) - 1) returns the text before the first comma, and =RIGHT(B2, 5) returns the ZIP when every ZIP has exactly five digits (on row 3’s 78701-2233 it returns -2233). The city needs MID with two FINDs, and the street and city formulas break on an extra comma the same way TEXTSPLIT does.
Why does my ZIP code lose its leading zero?
Excel treats 02108 as the number 2108 whenever the column is General: when you type it, open a CSV, or split with Text to Columns. =TEXT(F2, "00000") brings it back as text. Format Cells → Special → Zip Code only changes what the cell shows. The cell still holds 2108, and a mail merge can read it without the zero.
How do I split an address that has no commas?
Only the end can be split by position. =TEXTAFTER(TRIM(B2), " ", -1) returns the ZIP, and =TEXTBEFORE(TEXTAFTER(TRIM(B2), " ", -2), " ") returns the state. Where the street ends and the city starts depends on the words themselves, so either add the commas by hand or use a tool that reads the address. In the video on this page, Excel Data Cleaner splits addresses with no commas at all.