How to Separate Address in Excel with Comma (3 Easy Methods)

Often we get a list of addresses in a column and we need to separate them into street addresses, city, state, and ZIP codes. So, you may want to learn how to separate addresses with Comma in Excel. In this article, I will show 3 different methods to separate address in Excel with comma. Also, you can use these methods to separate address with dash, dots, or any other. In addition, you will be able to split any texts or numbers using these methods.


Download Practice Workbook

You can download the practice workbook from here:


3 Simple Methods to Separate Address in Excel with Comma

Suppose you have a list of people with full addresses and now you want to separate the address into columns to get the values of street, city, state, and ZIP code. I am showing you 3 easy and quick methods to separate the addresses which contain a comma between the values. Also, you can use these methods for dots or dash separators.

How to Separate Address in Excel with Comma


1. Use Excel Flash Fill Feature

When you have a list of addresses in a similar pattern you can use the Flash Fill feature. Like when the address is separated with only commas or street addresses and the city is separated with hyphens and others are in commas but all are in the same pattern. Using flash fill in the list of addresses, Excel will auto-detect the pattern and fill the cells.

When to Use Flash Fill Feature to Separate Address in Excel:

  • The addresses are separated with a similar pattern.
  • When you will not change the addresses anymore because with change it will not update the separated data columns.

To use the flash fill feature, follow the steps below:

πŸ“Œ Steps:

  • First, you have to manually input the values of the street address, city, state, and ZIP code in the first row from the given address.

How to Separate Address in Excel with Comma

  • Then, click on the second row for street address and go to Data tab > Flash Fill tool. Or you can use simple shortcuts Ctrl+E to call the Flash Fill feature.

How to Separate Address in Excel with Comma

  • As a result, you will see the column is flash filled with street addresses which were separated with commas.

How to Separate Address in Excel with Comma

How to Separate Address in Excel with Comma

  • But, here you will get a problem. You will see that the ZIP codes starting with 0 are missing the first 0 and became 4 digits.

How to Separate Address in Excel with Comma

  • For this, you convert the zip codes to TEXT format. There are many ways to convert or format Numbers to Text. Also, there are ways to add missing 0’s in front of numbers to make them of specific digits. I am showing the easiest way here.
  • Add an Apostrophe in front of the number of the first cell of the column. Thus it will convert the Number into the Text format.

How to Separate Address in Excel with Comma

  • Now, use the Flash Fill feature again to extract ZIP codes from the addresses.
  • Finally, you have separated addresses of similar patterns into parts in Excel.

Flash Fill Feature to Separate Address in Excel


Similar Readings


2. Use Text to Columns Wizard

You can find another feature named Text to Columns in Excel with which you can separate texts or addresses which contain only one type of delimiters.

When to Use Text to Columns Feature to Separate Address in Excel:

  • The addresses are separated with only one delimiter like only commas or dash or hyphens or dots.
  • When you will not change the addresses anymore because with change it will not update the separated data columns.

To use the flash fill feature, follow the steps below:

πŸ“Œ Steps:

  • First, select the cells of the address column.
  • Then, go to the Data tab > Text to Columns Feature.

Using Text to Columns Feature

  • Then, a new window will appear. Select the β€œDelimited” option and press the Next button.

Using Text to Columns Feature

  • Now, mark the comma box as the delimiter and press the Next button again.

Using Text to Columns Feature

  • Then,Β  a new window will appear. Here select the ZIP code column in the Data Preview section and select Text as the column data format. So, the ZIP code won’t lose any digits.
  • Now, select the cell where to start the separated columns and the press β€œFinish” button.

Using Text to Columns Feature

  • As a result, you will see the separated columns will create and the ZIP code will create in TEXT format.

Using Text to Columns Feature


3. Combine LEFT, RIGHT and MID Functions

You also can use formulas to separate addresses in columns to extract the value of the street address, city, state, and ZIP codes.

When to Use Excel Formulas to Separate Address in Excel

  • When you will change the address columns and want to get the separated address in columns automatically.
  • When the data is imported from any website or server system.

πŸ“Œ Step 1: Find the Position of Delimiter-Comma

To use formulas, you have to detect the position of the commas in the address. As you have 3 separations in the address cells so you will get 3 commas. So, create 3 columns to get the positions of 3 commas. Follow these steps to use the FIND function to find the position of the delimiters.

  • At first, put the delimiter comma in cell N2.
  • Paste this formula into cell M5 to get the position number of the 1st comma which is separating the value of the street number and the city name.
=FIND($N$2,D5)

Find the Position of Delimiter Comma

  • Then, you have to find the position of the second comma which is separating the name of the city and the ZIP Code.
  • To get the position value of the 2nd comma, paste this link into the cell N5
=FIND($N$2,D5,M5+1)

Find the Position of Delimiter Comma

  • Then use the Fill Handle icon to drag the formula to the other cells also.

Find the Position of Delimiter Comma

πŸ“Œ Step 2: Extract Street Address from Address Using LEFT Function

Now, you have the position of separating commas in columns. So, you can separate the address using Excel formulas. To extract the street address which is on the leftmost side of the address, paste this link into cell E5.

=LEFT(D5,J5-1)

πŸ”Ž Formula Breakdown:

  • Here, the D5 cell contains the address value.
  • J5 cell contains the position of the 1st comma.
  • So, the LEFT function will give the leftmost characters which are before the 1st comma.

Extract Street Address from Address Using LEFT Function

πŸ“Œ Step 3: Extract the City Name from Address Using MID Function

Now, you extract the city name from the address which places between the 1st and 2nd comma. Use this formula to separate city name from address using the MID function.

=MID(D5,J5+1,K5-J5-1)

πŸ”Ž Formula Breakdown:

  • D5: cell contains the address value.
  • J5 is the position of the 1st comma and J5+1 will give the starting character of the city name
  • K5 is the position of the 2nd comma
  • K5-J5-1 will give the character length of the city name.
  • So, now the MID function will give the character between the 1st and 2nd comma as the city name.

Extract the City Name from Address Using MID Function

πŸ“Œ Step 4: Extract the ZIP Code from the Address Using the RIGHT Function

In general, the ZIP code is of 5 digits and placed at the end of an address. So, you can use the RIGHT function to extract the last 5 characters of an address. Paste this formula into the cell H5:

=RIGHT(D5,5)Β 

Extract the ZIP Code from the Address Using the RIGHT Function

  • Now, use the fill handle icon to drag all the formulas to the other rows also.

Using LEFT, RIGHT and MID Functions to Separate Address

  • Finally, you have separated the address with commas using Excel formulas.

Read More: How to Format Addresses in Excel (4 Easy Methods)


Things to Remember

  • Flash Fill feature is the quickest method to separate address in Excel if all address is of a similar pattern
  • Text to Columns will be useful if they are not of a similar pattern but are separated with any delimiter.
  • The formula should be used if you want to make a dynamic worksheet that will change automatically while changing the data.

Conclusion

In this article, you have found how to separate address in Excel with comma. You can use the Text to Column feature or Flash Fill feature to extract separate street names, cities, states, and ZIP codes. Also, you can use individual formulas LEFT, MID, and RIGHT to separate address in columns with comma in Excel. hope you found this article helpful. You can visit our website Exceldemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

Osman Goni Ridwan

Osman Goni Ridwan

I am Ridwan, graduated from Naval Architecture and Marine Engineering Dept, BUET, currently residing in Dhaka, Bangladesh. And my passion is to grow up my skillsets with industry demands. My prime goal is to be a data analyst as I do love to solve problems and play with data.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo