How to split an address into columns in Excel

Why a plain split goes wrong

A customer list came from a web form with the whole address in one cell. You need street, city, state and ZIP in separate columns for a mail merge or a shipping upload.

The obvious formula is =TEXTSPLIT(A2,", "), and for "12 Elm St, Springfield, IL 62704" it works: three parts. But real data isn't that tidy. The San Francisco address has a Suite line, so it splits into four parts and the city lands in the state column. The Boulder address has no space after the first comma, so splitting on ", " gives only two parts.

=TRIM(TEXTSPLIT(A7,",")) fixes the missing space: split on the comma alone, then TRIM the spaces. It still leaves the state and ZIP together, and it still shifts columns when a Suite line appears. The fix is to count from the right.

ZIP and state: the end of the address

TEXTAFTER with a negative instance number counts delimiters from the end. =TEXTAFTER(A2," ",-1) returns everything after the last space, which is always the ZIP: 62704.

The state is after the last comma. TEXTAFTER(A2,",",-1) gives " IL 62704", TRIM removes the leading space, and LEFT(...,2) keeps the two-letter code: IL. If your list includes longer state names, use =TEXTBEFORE(TRIM(TEXTAFTER(A2,",",-1))," ",-1) instead, which keeps everything before the ZIP.

City and street, even with a Suite line

The city is always between the second-to-last comma and the last one. TEXTAFTER(A3,",",-2) gives " San Francisco, CA 94105", TEXTBEFORE(...,",") cuts it at the next comma, and TRIM tidies it: San Francisco.

The street is everything before the second-to-last comma: =TEXTBEFORE(A3,",",-2). For the San Francisco row that's "480 Market Street, Suite 210", so the Suite line stays with the street, where a label or shipping form expects it. For the plain rows it's just the street, like 12 Elm St.

If you need the unit in its own column and it's written inside the street, as in "1500 Lake Shore Dr Apt 5B", split on the word: =TEXTBEFORE(A6," Apt ") gives 1500 Lake Shore Dr and =TEXTAFTER(TEXTBEFORE(A6,","),"Apt ") gives 5B. Wrap them in IFERROR for rows with no Apt.

All four columns for the whole list

In Excel 365, give each formula the whole range and HSTACK the results side by side. One formula fills the table for every row: the Suite line stays with its street, Boulder is found despite the missing space, and the Austin ZIP+4 78701-2315 is trimmed to 78701 by LEFT(...,5).

Keep the ZIPs as text. TEXTAFTER returns text, so Portland's 04101 keeps its leading zero (=ISTEXT shows TRUE). If you convert it with VALUE or --, it becomes 4101. If ZIPs have already lost their zeros, =TEXT(4101,"00000") brings back 04101.

If some addresses don't follow the street, city, state ZIP pattern at all (a PO box with no commas, or a country at the end), the formulas return #N/A or the wrong piece. Filter for those rows and fix them by hand, or paste a sample into Cellwise Fix (cellwise.app/excel-formula-helper/) to get a formula for your exact format.

No formulas: Text to Columns or Flash Fill

In older Excel, or for a one-off cleanup, Text to Columns splits on the comma. It changes the data in place, so work on a copy of the column.

Flash Fill also works on tidy lists: type the city for the first two rows next to the addresses, then press Ctrl+E. Check the results, because Flash Fill guesses from your examples and tends to stumble on the Suite and Apt rows.

1. Copy the address column to a spare column and select it.
2. Data > Text to Columns > Delimited > Next.
3. Tick Comma (and untick Tab), then Next.
4. Click the column that will hold "IL 62704" and choose Column data format: Text, so ZIPs keep their leading zeros. Click Finish.
5. To separate state and ZIP, select that column and run Text to Columns again with Space as the delimiter, again setting the ZIP column to Text. Then use Home > Find & Select > Replace or TRIM to remove the leading spaces left by the first split.