Parsing addresses into separate columns for street, city, and zip code is essential for data organization and analysis. Excel provides several functions, including LEFT, RIGHT, MID, FIND, and LEN, to help split addresses into their respective components.
Split Addresses into Separate Columns for Street, City, and Zip Code
Using Excel functions, we can easily extract parts of an address and place them into separate columns for better readability and usability.
Example
Below is an example dataset showing full addresses. This dataset will be used to demonstrate how to parse addresses into street, city, and zip code.
| Full Address | Street | City | Zip Code |
|---|---|---|---|
| 123 Main St, Springfield, 12345 | 123 Main St | Springfield | 12345 |
| 456 Elm St, Shelbyville, 67890 | 456 Elm St | Shelbyville | 67890 |
Exercise
Parse the following addresses into separate columns for street, city, and zip code using the appropriate Excel functions:
Use the functions to extract the street, city, and zip code from each address.
Solution
To parse the addresses, follow these steps:
Apply Functions:
Street: Use the LEFT and FIND functions to extract the street.
=LEFT(A2, FIND(",", A2) - 1)
=LEFT(A3, FIND(",", A3) - 1)City: Use the MID and FIND functions to extract the city.
=MID(A2, FIND(",", A2) + 2, FIND(",", A2, FIND(",", A2) + 1) - FIND(",", A2) - 2)
=MID(A3, FIND(",", A3) + 2, FIND(",", A3, FIND(",", A3) + 1) - FIND(",", A3) - 2)Zip Code: Use the RIGHT and FIND functions to extract the zip code.
=RIGHT(A2, LEN(A2) - FIND(",", A2, FIND(",", A2) + 1) - 1)
=RIGHT(A3, LEN(A3) - FIND(",", A3, FIND(",", A3) + 1) - 1)By using these functions, you can effectively parse addresses into separate columns for street, city, and zip code, making the data more organized and easier to analyze. This setup ensures the user can visualize and understand how to use Excel tools for address parsing.