When you have an address with commas in Excel, you can simply use the TEXTSPLIT function to separate the address into multiple cells.
However, if you have an address that does not have commas then you must use the Flash Fill feature in Excel to separate the address into multiple cells.
The following example shows how to do so.
Example: How to Separate Addresses Without Commas in Excel
Suppose we have the following list of addresses in Excel:
Suppose we would like to split the addresses into separate cells that contain the street address, city, state, and zip code all in their own cells.
To do so, we need to first manually enter the correct values for the street, city, state and zip code for the first address:
Next, we can highlight the cell range B2:B9:
With this range highlighted, we can then click the Fill button within the Editing group on the Home tab and click the Flash Fill option from the dropdown menu:
This tool will automatically fill in the correct street address for each row:
Use the Flash Fill feature again on the city, state and zip code columns to fill in the correct values:
Notice that each address has been successfully split into separate cells that show the street address, city, state and zip code.
Note: The Flash Fill feature in Excel is useful when you are able to manually provide an example of the correct value that should be filled in and then you can let the Flash Fill identify the pattern in your data and fill in the remaining values.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: How to Count If Cells Contain Text
Excel: How to Split Word into Individual Letters
Excel: How to Extract Text Between Two Characters
