How to Separate Address in Excel?

When you work with addresses, it can be convenient sometimes to have different parts of the address split out into Street address, city, state, and zip code (or postal code).

This can make it easy for you to filter, sort, and query the data, or use it for other purposes.

So, in case you want to sort people by state, it would help if you had one column containing only the state part of the people’s addresses.

In this tutorial we will look at three ways to separate an address in a cell into separate columns, each containing a specific portion of the address:

using Excel’s Text-to-columns feature
using Excel’s Flash fill feature
using LEFT, RIGHT and MID functions

Table of Contents

How to Separate Address in Excel Using Text-to-columns Feature

Consider the following list of addresses:

In the above screenshot, you will notice that all the addresses have a comma delimiting their different parts.

This is really convenient because now we can make use of this comma delimiter to separate out the different parts of the address with the Text-to-columns feature.

Here are the steps to do this:

Put headings for each of the new columns that will hold different parts of the addresses, as shown below:

Select all the addresses that you want to split (cells A2:A7 in our example).
Select the Data tab.
Under the ‘Data Tools’ group, click on the ‘Text-to-columns’ button.

This will open the ‘Convert Text to Columns Wizard’.
In step 1 of 3, make sure that the radio button next to the ‘Delimited’ option is selected.

Click Next
In step 2 of 3, under the ‘Delimiters’ group, make sure that the checkbox next to ‘Comma’ is selected.

Click Next.
In Step 3 of 3, change the ‘Destination’ cell to the one where you want to see the output. In our case, we wanted the result in cell B2, so we changed the destination value to $B$2.

Select destination for the address split

Click Finish.

You will now find your full address split into three parts.

The first two columns now contain the Street Address and City, while the last column (column D) contains both the State and the Zip code.

These two parts did not get split because they don’t have a comma separating them in the original address.

Notice that the State and Zip-code are separated by a space. So to split these two, do the following:

Select all cells of the column D and repeat steps 3 to 9. Only difference is that in step 7, make sure that the checkbox next to ‘Space’ is selected, instead of ‘Comma’.

After that, simply click on the Finish button.

Your address should now be split into its constituent parts:

Note: If you don’t want to keep the original addresses, you can always skip steps 9 and 10. This will cause the split data to be overwritten on the original.

Now, what if your addresses were separated into separate lines instead of using comma delimiters as shown below?

In this case, instead of Step 8, you can check the box next to ‘Other’ and in the input box next to it, type CTRL+J on your keyboard.

You will not see anything in the box, but if you look closely for a while, you will see a tiny blinking dot.

Select Other and Control J as the delimite

When you click Finish, your addresses should get separated into their parts as shown below:

Address split where there was a new line character

Also read: Extract ZIP Code from Address in Excel

How to Separate Address in Excel Using Flash Fill

The first method that we discussed works great if you have a single delimiter consistently demarcating the different parts of the address.

But consider a case where you have a number of different delimiters, like the list of addresses shown below:

In the above list of addresses, you will notice that a hyphen separates the Street address from the City, a comma separates the City from the State and space separates the State from the Zip code.

In cases such as these, you might not want to use the Text-to-columns feature. A better option would be to use Excel’s ‘Flash fill’ feature.

The Flash fill feature lets you extract information from cells based on patterns in your data.

So if you use flash fill on your addresses, it can detect the parts of the address that you want to extract in each cell and then simply use this pattern for all the other addresses.

Let us see how to use Flash fill to split our addresses into different columns:

Put headings for each of the new columns that will hold different parts of the addresses, as shown below:

Fill out the first two rows of columns B to E with the appropriate parts of the address, as shown below:

Note: For the Zipcode column, add an apostrophe before the codes, in case any of them start with a 0.

Click on cell B3.
From the Data tab, click on the Flash Fill button (under the ‘Data Tools’ group)

This will copy the pattern to all cells in column B, so that you get the ‘Street address’ part of the Full address in all rows of column B.

Repeat steps 3 and 4 for cells C3, D3 and E3.

You should now find your full address split into its parts in different columns.

Also read: Extract Last Word in Excel

How to Separate Address in Excel Using LEFT, RIGHT and MID Functions

Finally, let us see a more flexible approach. To extract the different parts of the address, you can use a set of Excel formulae.

This method can be a little complex, but you can use the techniques explained to customize it to the address patterns you have.

We will use the same address list here:

Let’s start extracting part by part:

Extracting the Street Address

The street address is the first part of the full address. It starts from the extreme left and ends in a hyphen symbol.

Notice that all the addresses in the list follow this pattern.

Therefore, to extract the Street address from say, the first Full address (cell A2), we can use the LEFT function as follows:

=LEFT(A2,FIND("-",A2)-2)

The LEFT function extracts the given number of characters from the left side of a given text.

In this formula, the LEFT function takes 2 parameters:

The text from which you want to extract characters (cell A2)
The number of characters you want to extract, starting from the leftmost one (FIND(“-“,A2)-2)

Here, the second parameter uses the FIND function to calculate how many characters we need to extract from the original Full address.

It does this by finding the position of the hyphen character (“-“) and then subtracting 2 from this number.

In cell A2, the hyphen character is at position 21, so the second parameter has the number 21-2=19.

The LEFT function finally extracts all characters from the first to the 19^th position of the full address. All these extracted characters belong to the Street Address.

Extracting the City

The City part of the Full address comes right after the Street address.

It starts from the right after the hyphen (in our example) and ends in a comma.

So to extract the City from the first Full address (cell A2), we can use the MID function as follows:

=MID(A2,FIND("-",A2)+2,FIND(",",A2)-LEN(B2)-4)

The MID function extracts the given number of characters starting from a given position of a given text.

In this formula, the MID function takes 3 parameters:

The text from which you want to extract characters (cell A2)
The position of the character you want to start extracting from, FIND(“-“,A2)+2
The number of characters you want to extract, FIND(“,”,A2)-LEN(B2)-4

Here, the second parameter uses the FIND function to calculate the position of the character you want to start extracting from in the original Full address.

It does this by finding the position of the hyphen character (“-“) and then adding 2 to this number.

In cell A2, the hyphen character is at position 21, so the second parameter has the number 21+2=23.

This means the MID function will start extracting the characters from position 23 of the Full address (cell A2)

The third parameter uses the FIND function to calculate how many characters we need to extract.

It does this by finding the position of the comma (“,“) and then subtracting the length of the street address from it (which is in cell B2).

This still does not consider the two spaces and the hyphen after the street address, so we subtract 4 more from this difference.

In cell A2, the comma is at position 28, and the length of the street address (in cell B2) is 19, so the second parameter has the number 28-19-4=5.

The MID function finally extracts all characters from the 23rd position of the Full address up to the 27^th one. All these extracted characters belong to the name of the City.

MID function to extract city name from addresss

Extracting the State

The State part of the address comes right after the City. It’s just 2 characters following the comma symbol.

So to extract the State from the first Full address (cell A2), we can use the MID function as follows:

=MID(A2,FIND(",",A2)+2,2)

In this formula, the MID function takes 3 parameters:

The text from which you want to extract characters (cell A2)
The position of the character you want to start extracting from, FIND(“,”,A2)+2
The number of characters you want to extract, 2

Here, the second parameter uses the FIND function to calculate the position of the character you want to start extracting from.

It does this by finding the position of the comma and then adding 2 to this number since the State initials start with 2 characters after the comma.

The third parameter is just 2 because the State initials are just 2 characters long.

The MID function finally extracts the two State characters starting from position from the 30^th position of the full address up to the 32^nd one.

These two extracted characters belong to the name of the State.

Extracting the Zip code

The Zip code is the last part of the full address. It can be either 5 or 9 digits long, and it comes right after the state code.

So to extract the zip code from the first address (cell A2), we can use the MID function as follows:

=MID(A2,FIND(D2,A2)+3,9)

In this formula, the MID function takes 3 parameters:

The text from which you want to extract characters (cell A2)
The position of the character you want to start extracting from, FIND(D2,A2)+3
The number of characters you want to extract, 9

Here, the second parameter uses the FIND function to calculate the position of the character you want to start extracting from.

It does this by finding the position of the State initial (which is in cell D2) and then adding 3 to this number, since the State name is two characters long, followed by a space.

The third parameter could be made either 5 or 9, depending on the length of the zip code, but we specified the larger of the two numbers to account for both cases.