09-03-2019 11:15 AM
09-03-2019 03:22 PM
your question is not very clear. I think you have address information in one column, including name, street, town, zip code, and you want to split that data into separate columns.
This is not an easy challenge, since names can have two, three, four words, streets can have several words, towns can have several words. So, in order to automate splitting the data with a formula or some logic, Excel needs to understand where the name ends, where the street starts and ends, where the town starts and ends, etc.
Joe Bloggs, 123 ABC Street, Clearwater, WA, 1245
In this example, the comma separates the elements and can be used to determine the difference between name, street, town, state and zip code.
Joanna Sims Dark Grove Lake Weather CA 01234
In this example, only the zip code and the state are clear. The rest cannot be clearly identified.
What is your data like?
09-04-2019 02:50 AM
09-04-2019 02:11 PM - edited 09-04-2019 02:16 PM
@robbie2130 just using a space as a separator to divide the data into columns will not give you good results if the data is not uniform. You can use Text to columns, but you may end up with a situation where you can't tell which column has the city. I color-coded name, address, city, state and zip, but how would Excel know which cell(s) to use for the city?
09-06-2019 02:43 AM
09-08-2019 02:58 PM - edited 09-08-2019 03:00 PM
@robbie2130 That's what I've been trying to tell you. How does Excel identify where the address ends and the city starts????
The data needs to have some pointers that Excel can use as rules. What are these rules?
It looks like your data is not suitable for this purpose, because it cannot be determined with logic how to split up the rows. Each row may be different and there are no pointers that Excel can use to split the data up correctly.
This is called "garbage in - garbage out".
If you want a better result, you need better data first.
Don't shoot the messenger.
by FrauPhilipp on August 08, 2020
by Guy Hunkin on August 05, 2020
by Sonia Atchison on July 24, 2020
Posted in Microsoft Ignite The Tour 2019 on February 14, 2020