You can use the following formula in Excel to split an address into multiple cells:
=TEXTSPLIT(A2, ",")
This particular formula splits the address in cell A2 into separate cells that contain the individual parts of the address that are separated by commas.
The following example shows how to use this formula in practice.
Example: How to Split Addresses into Multiple Cells 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.
We can type the following formula into cell B2 to split the first address into multiple cells:
=TEXTSPLIT(A2, ",")
We can then click and drag this formula down to each remaining cell in column B:
Notice that the TEXTSPLIT function has successfully split each address into individual cells based on where the commas occur in the original address.
If you’d like, you can also add column headers to specify which columns represent different parts of the address:
We can now see which columns represent the street address, city, state and zip code.
Note: You can find the complete documentation for the TEXTSPLIT function in Excel here.
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
