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

Get FREE Advanced Excel Exercises with Solutions!

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.


How to Separate Address in Excel with Comma: 3 Easy Methods

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

  • Now, similarly do the same thing to separate city, and ZIP code from the addresses

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

Read More: How to Separate City State and Zip from Address Using Excel Formula


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

Read More: How to Separate City and State in Excel


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 Separate City and State without Commas in Excel


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.

Download Practice Workbook

You can download the practice workbook from here:


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. Please, drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles


<< Go Back to Address Format | Text Formatting | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Osman Goni Ridwan
Osman Goni Ridwan

OSMAN GONI RIDWAN is a marine engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, Excel, and VBA and goes beyond the basics. Armed with a B.Sc in Naval Architecture & Marine Engineering from BUET, OSMAN has transitioned into the role of a content developer. Specializing in creating technical content exclusively centered around Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo