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

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

Consider the following list of addresses:

Full address dataset

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:

  1. Put headings for each of the new columns that will hold different parts of the addresses, as shown below:
Add headings in adjacent columns
  1. Select all the addresses that you want to split (cells A2:A7 in our example).
  2. Select the Data tab.
  3. Under the ‘Data Tools’ group, click on the ‘Text-to-columns’ button.
Click on Text to Columns
  1. This will open the ‘Convert Text to Columns Wizard’.
  2. In step 1 of 3, make sure that the radio button next to the ‘Delimited’ option is selected.
Select Delimited as the first step
  1. Click Next
  2. In step 2 of 3, under the ‘Delimiters’ group, make sure that the checkbox next to ‘Comma’ is selected.
Select comma as the delimited
  1. Click Next.
  2. 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
  1. 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.

Final data where address has been split

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

  1. 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’.
Select space as the delimiter
  1.  After that, simply click on the Finish button.

Your address should now be split into its constituent parts:

State and Zip code has also been split

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?

Full address dataset with new lines

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

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:

Full address dataset

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:

  1. Put headings for each of the new columns that will hold different parts of the addresses, as shown below:
Add headers in adjacent columns
  1. Fill out the first two rows of columns B to E with the appropriate parts of the address, as shown below:
Fill out the expected result

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

Add apostrophe before zip code
  1. Click on cell B3.
  2. From the Data tab, click on the Flash Fill button (under the ‘Data Tools’ group)
Click on Flash Fill
  1. 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.
Flash fill copies the pattern
  1. 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.

Final separate address using flash fill

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:

Full address dataset

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:

  1. The text from which you want to extract characters (cell A2)
  2. 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 19th position of the full address. All these extracted characters belong to the Street Address.

LEFT function to extract 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:

  1. The text from which you want to extract characters (cell A2)
  2. The position of the character you want to start extracting from, FIND(“-“,A2)+2
  3. 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 27th 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:

  1. The text from which you want to extract characters (cell A2)
  2. The position of the character you want to start extracting from, FIND(“,”,A2)+2
  3. 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 30th position of the full address up to the 32nd one.

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

MID function to get the state name

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:

  1. The text from which you want to extract characters (cell A2)
  2. The position of the character you want to start extracting from, FIND(D2,A2)+3
  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.

MID function to get the zip code

Here’s how the worksheet finally looks after copying the formulae of cells B2:E2 down to the rest of the cells:

Final data where address has been split using formulas

 In this tutorial, we showed you three ways to separate an address into different columns in Excel.

We hope you found it helpful and easy to understand.

Other articles you may also like: