How to split 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 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