Osman Goni Ridwan
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.
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.
- 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.
- As a result, you will see the column is flash filled with street addresses which were separated with commas.
- Now, similarly do the same thing to separate city, and ZIP code from the addresses
- 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.
- 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.
- 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.
Read More: How to Separate City State and Zip from Address Using Excel Formula
Similar Readings
- Make an Address Book in Excel
- How to Separate Address in Excel
- How to Separate Address in Excel Using Formula
- How to Separate City and State without Commas in Excel
- How to Separate Address Number from Street Name in Excel
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.
- Then, a new window will appear. Select the “Delimited” option and press the Next button.
- Now, mark the comma box as the delimiter and press the Next button again.
- 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.
- As a result, you will see the separated columns will create and the ZIP code will create in TEXT format.
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)
- 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)
- Then use the Fill Handle icon to drag the formula to the other cells also.
📌 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.
📌 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.
📌 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) 
- Now, use the fill handle icon to drag all the formulas to the other rows also.
- Finally, you have separated the address with commas using Excel formulas.
Read More: How to Format Addresses 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.
