Advanced Text To Columns

[psgolfer32381]

Hi,

I have a spreadsheet that has a whole set of address data in each cell for quite a few rows. I cannot figure out how to get it separate out into separate columns for address #1 (the location), address #2 (the street address), city, state, and zip. The way the data is in each cell is not consistent with the spacing.

I've attached the spreadsheet I'm trying to work with. I'm referring to column D (it was easy enough to separate out the info that is now in column C).

Thanks so much!

Michael

[mikeTRON]

Yikes, this looks like a giant pain.

Is there not a way to ask for the data in a standardized manner?

[psgolfer32381]

We are trying to get the data in a better format, but so far no luck. I have had no luck making this work so far myself or by reaching out to folks here at work. I'm thinking there's bound to be somebody who has run across this before and found a solution!

[Glenn Kennedy]

Horrible. No real pattern. However try BF & I. Brute Force and Ignorance.

[Arkadi]

Considering sometimes there isn't even a space before the city name, I don't see how this can be done. Without ANY pattern whatsoever, code won't be able to do this. A macro can probably be created to work with many of the scenarios, but you'll still have errors in the data no matter what (afaik)

[Glenn Kennedy]

Simpler formula that might also work for State and ZIP.

=IFERROR(TRIM(MID(D2,FIND(",",D2)+1,255)),"")

[AlKey]

Unfortunately, the data is very inconsistent and this what makes this task very difficult.

Please see attached file.