How to split addresses in Excel
By
SpreadCheaters
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 split the address into its elements.
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 addresses have been split into individual fields
