How to Split an Address Into Columns in Excel

To split an address in Excel, you need a character that separates the parts, and in most address lists that is a comma. With commas in place, TEXTSPLIT puts the street, city and "state ZIP" into separate columns in one step, and TEXTBEFORE and TEXTAFTER pull out any single part. This guide covers those formulas, a version for older Excel, and the no-formula options.

Which method should you use to split an address?#

The best method depends on your Excel version and whether the list will change. Here is a quick guide:

All the examples use US-style addresses in column A, like this one in A2:

18 Elm Street, Riverton, OH 43210

How to split an address into columns with TEXTSPLIT#

TEXTSPLIT cuts text wherever it finds a delimiter (the character or characters that separate the parts) and spills the pieces into the cells to the right. In B2, type:

=TEXTSPLIT(A2,", ")

Use ", " (comma and space) as the delimiter, not just ",". Otherwise every part after the first starts with a space. Leave the cells to the right empty, or the formula shows #SPILL!. Fill the formula down for the other addresses. The TEXTSPLIT function guide and the article on TEXTSPLIT in Excel cover its other arguments.

TEXTSPLIT leaves the state and ZIP together, because they are separated by a space, not a comma. You can't simply split on spaces too, because "18 Elm Street" would break into three pieces. The next section handles the last part separately.

Pull out the street, city, state and ZIP with TEXTBEFORE and TEXTAFTER#

TEXTBEFORE returns the text before a delimiter and TEXTAFTER returns the text after it. A negative instance number counts from the end of the text, which is the key to splitting the "state ZIP" part:

Read the city formula from the inside out. TEXTAFTER(A2,", ") drops the street and gives "Riverton, OH 43210". TEXTBEFORE then keeps the part before the next comma: Riverton. The ZIP formula takes everything after the last space, so it also works for ZIP+4 codes like 43210-1234.

These two functions are the same tools used for splitting full names and extracting the domain from an email.

Handle extra parts like apartment numbers#

Some addresses have an extra part, such as "55 Pine Court, Apt 3, Lakeside, CA 92040". Now TEXTSPLIT returns four columns instead of three, and the city formula above returns "Apt 3".

The fix is to count from the end, because the city, state and ZIP are always the last parts:

TEXTAFTER(A2,", ",-2) means "after the second-to-last comma", which gives "Lakeside, CA 92040" for both short and long addresses. Use these versions whenever your list mixes address styles.

Split an address in older Excel with LEFT, MID and FIND#

TEXTSPLIT, TEXTBEFORE and TEXTAFTER need Microsoft 365 or Excel 2024. In older versions, combine LEFT, MID and RIGHT with FIND, which returns the position of a character in the text:

The first comma is at position 14, so LEFT keeps the 13 characters before it. The city formula finds the second comma by starting FIND one character after the first, then takes the characters in between. The state and ZIP formulas assume a two-letter state and a five-digit ZIP, so check any rows that don't fit. The FIND function guide and extracting text between two characters explain the position maths.

Split an address without formulas#

For a one-off job, the built-in tools are quicker than formulas, but the results don't update when the address changes.

Text to Columns:

1. Select the addresses in column A.
2. Go to Data > Text to Columns, choose Delimited and click Next.
3. Tick Comma, click Next.
4. Select the last column in the preview and set it to Text if any ZIP codes start with 0. Click Finish.

The city and "state ZIP" columns will start with a space. Clean them with TRIM or Find and Replace. The comparison of Text to Columns vs TEXTSPLIT explains when each is better.

Flash Fill: type the city for the first address in the next column, then press Ctrl+E on Windows, or use Data > Flash Fill on a Mac. Excel copies the pattern down. Check the results, because Flash Fill guesses.

Clean messy addresses first#

Real address lists are rarely perfect. Before splitting, fix these problems:

• Double spaces or spaces before commas. Use =TRIM(A2) to remove extra spaces.
• Missing commas. "18 Elm Street Riverton OH 43210" has no reliable delimiter. Only the state and ZIP at the end can be split safely; fix the rest by hand or with Flash Fill.
• Line breaks inside the cell. Addresses pasted from labels often use a line break instead of a comma. Use CHAR(10) as the delimiter: =TEXTSPLIT(A2,CHAR(10)).

Practise it#

Splitting text into columns is a standard data-cleaning task in job tests. Practise TEXTBEFORE and TEXTAFTER in a live spreadsheet: you type the formula and it checks it instantly. Then try the modern Excel functions lesson for TEXTSPLIT and other spill formulas, or start with the exercise on this page.