How to Separate City, State, and ZIP in Excel

Last updated on November 9th, 2026 at 01:47 pm

To separate city, state, and ZIP code in Excel, split at the comma first and then at the space. In Excel for Microsoft 365 or Excel 2024, with “Austin, TX 78701” in cell A2, these three formulas do it: =TEXTBEFORE(A2,",") returns the city, =TEXTBEFORE(TRIM(TEXTAFTER(A2,","))," ") returns the state, and =TEXTAFTER(A2," ",-1) returns the ZIP code. In any version, Data > Text to Columns does the same job in two passes (comma, then space), and Flash Fill (Ctrl + E) can copy a pattern you type by hand.

Applies to: Excel for Microsoft 365 (Windows and Mac), Excel 2024, Excel 2021, Excel 2019 and Excel 2016. TEXTBEFORE, TEXTAFTER and TEXTSPLIT need Microsoft 365 or Excel 2024, according to Microsoft’s TEXTBEFORE function page. Everything else works in all of these versions.

Look at the address pattern first

The examples use this fictional list in column A, starting in A2, with a header in A1. Each cell follows the common U.S. pattern City, ST ZIP:

A (Address)B (City)C (State)D (ZIP)
2Austin, TX 78701AustinTX78701
3San Antonio, TX 78205San AntonioTX78205
4Boston, MA 02108BostonMA02108
5Kansas City, MO 64105-1234Kansas CityMO64105-1234
6Salt Lake City, UT 84101Salt Lake CityUT84101

Columns B to D show the result you’re aiming for. The list is deliberately awkward: some cities have two or three words, Boston’s ZIP code starts with 0, and Kansas City has a ZIP+4 code. A method that handles these five rows will handle most real lists. With city, state and ZIP in their own columns, the list is ready to print addresses on envelopes from Excel with a Word mail merge.

A few checks before you split anything:

  • Look at the pattern. Scroll through the column. If every row has a comma after the city and a space between the state and ZIP, any method below works. If some rows have no comma or spell out the state, see Troubleshooting.
  • Make room. Add headers such as City, State and ZIP in B1:D1, and make sure columns B to D are empty. Text to Columns and Flash Fill write over whatever is there.
  • Keep a copy. Copy the address column to another sheet if you plan to split it in place. Formulas leave the original alone.

Which method to pick:

  • Formulas update when an address changes. Best when the list is still being edited.
  • Text to Columns is a one-time split that works in every version.
  • Flash Fill is the fastest for a short, tidy list, but it guesses and doesn’t update.
  • Power Query repeats the same split every time you refresh, which suits lists you import each week or month.

How do I put city, state, and ZIP back together?

Use =B2&", "&C2&" "&D2, which rebuilds “Austin, TX 78701”. The same joining technique is covered in concatenating in Excel.

Method 1: TEXTBEFORE and TEXTAFTER formulas (Microsoft 365 and Excel 2024)

  1. Click cell B2 and type =TEXTBEFORE(A2,","), then press Enter. Excel returns everything before the comma: Austin.
  2. Click C2 and type =TEXTBEFORE(TRIM(TEXTAFTER(A2,","))," "). TEXTAFTER takes ” TX 78701″, TRIM removes the leading space, and TEXTBEFORE keeps the first word: TX.
  3. Click D2 and type =TEXTAFTER(A2," ",-1). The -1 tells Excel to count from the end, so it returns the text after the last space: 78701.
  4. Select B2:D2 and double-click the small square at the bottom-right corner of the selection (the fill handle) to copy the formulas down the list.
Excel worksheet with addresses in column A and the city, state and ZIP split into columns B, C and D by TEXTBEFORE and TEXTAFTER formulas
Split at the comma for the city, take the first word after the comma for the state, and use TEXTAFTER(A2," ",-1) for the ZIP. The formulas return text, preserving Boston’s 02108 and Kansas City’s 64105-1234.

Check rows 3 to 6: San Antonio and Salt Lake City come out whole because the city is cut at the comma, not at a space. Boston’s ZIP shows 02108 and Kansas City’s shows 64105-1234, because these functions return text and Excel doesn’t strip the zero from text.

Shortcut for state and ZIP: =TEXTSPLIT(TRIM(TEXTAFTER(A2,","))," ") in C2 spills the state into C2 and the ZIP into D2 with one formula. Leave D2 empty, or you’ll get a #SPILL! error. Don’t use TEXTSPLIT on the whole address with a space delimiter: it would split “Salt Lake City” into three cells.

Method 2: LEFT, MID and FIND formulas (Excel 2021 and earlier)

Older versions don’t have TEXTBEFORE or TEXTAFTER, but the classic text functions work in every version:

  • City (B2): =LEFT(A2,FIND(",",A2)-1) finds the comma’s position and keeps everything to its left.
  • State (C2): =MID(A2,FIND(",",A2)+2,2) starts two characters after the comma (skipping the comma and the space) and takes two letters.
  • ZIP (D2): =TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100)) returns the last word, whether it’s 5 digits or ZIP+4.

The ZIP formula looks odd, so here’s what it does: SUBSTITUTE swaps every space for 100 spaces, RIGHT grabs the last 100 characters (which can only contain the final word plus padding), and TRIM removes the padding. You’ll see =RIGHT(A2,5) suggested elsewhere. It’s fine if every ZIP has exactly five digits, but on “64105-1234” it returns -1234.

The state formula assumes exactly one space after the comma and a two-letter code. If your data has extra spaces, clean the column first with =TRIM(A2) in a helper column and point the formulas at that column.

Method 3: Text to Columns in two passes

The city ends at a comma, but the state and ZIP are separated by a space, so this takes two runs of the wizard. Doing it in one pass with both Comma and Space checked breaks “San Antonio” and “Salt Lake City” into separate columns.

Warning: Text to Columns replaces the selected cells with the first piece and fills the columns to the right with the rest. Work on a copy, or set a different Destination as shown below.

Pass 1: split off the city

  1. Select the addresses, A2:A6 in the example (not the header).
  2. Select Data > Text to Columns. The Convert Text to Columns Wizard opens.
  3. Choose Delimited and select Next.
  4. Under Delimiters, check Comma only (clear Tab if it’s checked). The Data preview shows the city in one column and the state and ZIP in the next.
  5. Select Next, set Destination to $B$2, and select Finish. Column B now holds the city and column C holds text such as ” TX 78701″.

Pass 2: split the state from the ZIP

  1. Select C2:C6.
  2. Select Data > Text to Columns, choose Delimited, and select Next.
  3. Check Space, clear Comma, and check Treat consecutive delimiters as one. Select Next.
  4. In the Data preview, click the ZIP column and choose Text under Column data format. This is the step that keeps Boston’s 02108 from turning into 2108.
Convert Text to Columns Wizard step 3 with the ZIP column selected in the preview and Column data format set to Text
In Step 3, select the ZIP column in Data preview and set its format to Text. The example destination is $C$2, so state and ZIP will occupy columns C and D. Finish remains pending in this recreated wizard.
  1. If the preview starts with an empty column (caused by the space left after the comma), click it and choose Do not import column (skip).
  2. Make sure Destination is $C$2 and select Finish. If Excel asks whether to replace the contents of the destination cells, select OK only if those cells hold nothing you need.

The steps match Microsoft’s Convert Text to Columns Wizard page. For the same wizard used on other kinds of data, see splitting columns in Excel.

Method 4: Flash Fill

Flash Fill watches what you type and repeats the pattern down the column. It’s in Excel 2016 and later on Windows and in current Mac versions.

  1. Format column D as text first: select column D, then Home > Number Format list > Text. Without this, a ZIP you type as 02108 becomes 2108.
  2. In B2, type Austin and press Enter.
  3. With B3 selected, press Ctrl + E (or select Data > Flash Fill). Excel fills the rest of column B.
  4. Repeat in column C (type TX) and column D (type 78701).

Check the multi-word cities and the ZIP+4 row. If Flash Fill gets one wrong, type the correct value in that row and press Ctrl + E again; a second example usually fixes the pattern. Flash Fill writes plain values, so the results won’t change if you later edit an address. The same technique is shown step by step in separating names into two columns. Microsoft’s Using Flash Fill in Excel page covers the automatic option.

Method 5: Power Query for lists you import again and again

Power Query records each step, so next month you paste or load the new list and refresh instead of repeating the split. If you can’t find it, finding Power Query in Excel explains where it lives in each version.

  1. Click any cell in the list and select Data > From Table/Range. Confirm the range and that My table has headers is checked, then select OK. The Power Query Editor opens.
  2. Select the Address column, then Home > Split Column > By Delimiter.
  3. Choose Comma, set Split at to Left-most delimiter, and select OK. You get Address.1 (city) and Address.2 (state and ZIP).
Power Query Split Column by Delimiter dialog with Comma chosen and Split at set to Left-most delimiter
Choose Comma and Left-most delimiter to split the city from the state and ZIP. This focused recreation follows Power Query Desktop, with Advanced options collapsed and OK pending.
  1. Select Address.2 and choose Transform > Format > Trim to remove the leading space.
  2. With Address.2 still selected, choose Split Column > By Delimiter again, pick Space, set Split at to Left-most delimiter, and select OK.
  3. Check the type icon in the ZIP column’s header. If it shows 123 (a number), click it and choose Text, so ZIP codes keep their zeros and ZIP+4 codes don’t cause errors.
  4. Double-click each header to rename it City, State and ZIP, then select Home > Close & Load.

To update later, change the source list and select Data > Refresh All. Microsoft’s Split columns by delimiter page describes the dialog options.

When the cell also holds a street address

For a full address such as “123 Main St, Austin, TX 78701”, the city sits between the last two commas. Counting commas from the end keeps things working even when the street itself has a comma, as in “450 Oak Ave, Suite 200, Boston, MA 02108”. In Microsoft 365 or Excel 2024:

  • Street: =TEXTBEFORE(A2,",",-2) returns “450 Oak Ave, Suite 200”
  • City: =TEXTBEFORE(TEXTAFTER(A2,", ",-2),",") returns “Boston”
  • State: =TEXTBEFORE(TEXTAFTER(A2,", ",-1)," ") returns “MA”
  • ZIP: =TEXTAFTER(A2," ",-1) returns “02108”

In older versions, the street is =LEFT(A2,FIND(",",A2)-1) and the city is =MID(A2,FIND(",",A2)+2,FIND(",",A2,FIND(",",A2)+1)-FIND(",",A2)-2). Those count from the first comma, so they only work when the street has no comma of its own; on the Suite 200 row the “city” comes back as “Suite 200”. The state and ZIP formulas from Method 2 still work on full addresses.

If you’d rather describe the pattern than count commas, REGEXEXTRACT in Microsoft 365 can pull pieces such as a 5-digit or ZIP+4 code. Using REGEXEXTRACT in Excel covers the syntax.

Keep ZIP codes and ZIP+4 codes intact

Excel treats 02108 as the number 2108 unless it’s stored as text. Formulas in Methods 1 and 2 return text, so they’re safe. Problems usually appear when a ZIP column is typed in, imported from a CSV file, or split with Text to Columns using the General format.

  • Bring back a missing zero: in a spare column, =TEXT(D2,"00000") turns 2108 into the text “02108”. Copy the results and paste them back as values if you want to replace the originals.
  • Display five digits without changing the value: select the cells, open Format Cells (Ctrl + 1), choose Special, and pick Zip Code or Zip Code + 4. Microsoft notes these types appear only when the locale is English (United States). See Display numbers as postal codes.
  • A column that mixes 5-digit and 9-digit numbers: Microsoft’s custom format [<=99999]00000;00000-0000 shows each one correctly.
  • Keep only the first five digits of ZIP+4: =LEFT(D2,5) turns “64105-1234” into “64105”. Keep the original column if mailing software needs the full code.

Formatting only changes what you see. If the ZIP codes will be exported to CSV or matched against another list, store them as text. More options are in stopping Excel from removing leading zeros.

Check the results and undo if needed

  • Spot-check the rows with two-word cities, ZIPs that start with 0, and ZIP+4 codes.
  • Filter the State column (Data > Filter) and look for anything that isn’t two capital letters.
  • Use =LEN(C2) to find states longer than 2 characters or ZIP codes that aren’t 5 or 10 characters long.
  • Undo Text to Columns or Flash Fill right away with Ctrl + Z (Command + Z on a Mac). For formulas, just delete the formula columns.
  • If you want plain values instead of formulas, select B2:D6, copy, then use Paste Special > Values.

#N/A and #VALUE! in address formulas

#N/A from TEXTBEFORE or TEXTAFTER

The cell doesn’t contain the delimiter, often because a row has no comma or is blank. Microsoft documents #N/A as the result when the delimiter isn’t found. Use the optional last argument to return something else: =TEXTBEFORE(A2,",",,,,"") returns an empty cell instead.

#VALUE! from FIND formulas

FIND returns #VALUE! when the comma is missing. Wrap the formula: =IFERROR(LEFT(A2,FIND(",",A2)-1),""). Then fix those rows by hand, since an address with no comma can’t be split reliably between a multi-word city and the state.

Results have extra spaces or the state formula returns the wrong letters

Imported data often has double spaces or non-breaking spaces from web pages. Clean a helper column with =TRIM(SUBSTITUTE(A2,CHAR(160)," ")) and split that column instead. Removing a space before text in Excel covers other stray-space fixes.

Full state names come out as “New”

The state formulas take the first word after the comma, so “Albany, New York 12207” gives “New”. Take everything between the comma and the last space instead: =TEXTBEFORE(TRIM(TEXTAFTER(A2,","))," ",-1) returns “New York”. It still works for two-letter codes.

Text to Columns overwrote my data

Press Ctrl + Z right away. Then insert empty columns or set the Destination in the last step of the wizard and run it again.

Flash Fill filled the wrong values or nothing at all

Flash Fill needs a consistent pattern and a typed example right next to the data. Add a second example, make sure there are no blank rows in the address column, and if it never offers a preview while you type, check that Automatically Flash Fill is turned on in the Advanced section of Excel’s Options, under Editing options. Ctrl + E still works when that option is off.

Does this work for Canadian or other non-U.S. addresses?

Partly. A Canadian postal code such as “K1A 0B1” contains a space, so the “last word” ZIP formulas return only “0B1”. For those rows, take the last seven characters with =RIGHT(A2,7), or use Flash Fill with an example.

Once the columns are split, sort or filter by state or ZIP, or use the clean columns as the source for labels and envelopes.