How to separate addresses in Excel
You can watch a video tutorial here.
Working with data in Excel frequently involves names and addresses such as those found in a customer database. You may need to summarize the data based on City or State and find that these fields are combined with the street address. To do your analysis, you need to separate the elements of the address.
Step 1 – Analyze the data
– Check the data to see how the fields are separated
– In this example, they are separated by a hyphen (-)
Step 2 – Open the Convert Text to Columns Wizard
– Select the data
– Go to Data > Data Tools
– Click on Text to Columns
– Click OK
Step 3 – Choose the file type
– Select the Delimited option as the addresses are separated (or delimited) by a hyphen
– Click on Next
Step 4 – Choose the delimiter
– Under Delimiters, choose Other and type in a hyphen(-)
– Check how the data has been separated in the Data preview
Step 5 – Choose the format and destination
– Under Column data format, choose General
– Select the Destination for the data
– Click Finish
Step 6 – Check the result
– The fields of the addresses are separated out into individual columns