splitting text string into different columns

**lbm1978** · 11-13-2013, 04:50 PM

I have a large data set of info in one column that I need separated into multiple columns. Can't use the "Text to Columns" tool because the text is not delimited nor fixed. Here is an example of some of the cells:
"Mary R. Anderson1865 W Choke Cherry DriveLouisville CO 80027"
"Sandy Andrews801 Meadowlark CoveLafayette CO 80026"
"Vujka ""VaVa"" S. Andrick4559 Beechcomber CourtBoulder CO 80301"
"Gaynel K. Andrusko3178 Eastwood CourtBoulder CO 80304"

I need these separated into columns for first name, last name, address, city, state, zip

Help!

**Ron Coderre** · 11-13-2013, 05:02 PM

Unless you can define a reliable structure pattern that we can use to parse the text, I doubt there is a formulaic or VBA solution you can have confidence in. Perhaps you can have the system that generated the list create something you can work with.

**Moo the Dog** · 11-13-2013, 05:13 PM

I can see how to extract the name from the address (so long as all addresses start with a #), then extract the state and ZIP from there...

But I have no idea how you would expect to separate the city from the address. I'm stumped.

Assuming your data is in A1, you can use this to extract the name:

Formula:

=LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&{0,1,2,3,4,5,6,7,8,9}))-1)

To extract the State, you could use:

Formula:

=LEFT(RIGHT(A1,8),2)

and for the ZIP:

Formula:

=RIGHT(A1,5)

- Moo
* Ultimately, I would suggest what Ron said above - find out if the system can output the data into a normalized data set (with some sort of delimiter preferably!)