seperating an address by street address, unit #, city, state and zip that has no commas

[michnance]

I have a 15,000 line spreadsheet where the addresses are listed with the street address then unit number then city then state code and zip separated by spaces within a single cell. A1 Example:

[TABLE="width: 358"]
<colgroup><col></colgroup><tbody>[TR]
[TD]23 NW 27th Lane Cape Coral FL 33993

I need the address to split like so:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]23 NW 27th Lane[/TD]
[TD]Cape Coral[/TD]
[TD]FL[/TD]
[TD]33993
[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
After trying myself I have adding all states listed by two letter state abbreviation in column I starting a row A, if that is helpful in anyway. If not, I can obviously deleted column.

I would so appreciate any help getting this split.

All searches are showing how to do it when there is a comma following street address and another following city. PLEASE HELP! ​

​

 

[sandy666]

you can post link to the shared excel file with your data (example of) using google drive, one drive or any similar
clean your PM box

 

[michnance]

sandy666:

thank you so much.... [FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]https://drive.google.com/file/d/0B4EIWJNJ3eVqaFdPdjNVZ3k3cTRoQWJGSEZKWThDeDJ0UUhr/view?usp=sharing[/FONT]

 

[sandy666]

ok, I'll see what I can do with it...
maybe I'll need some more info later

 

[michnance]

seriously cannot thank you enough!!!!

 

[sandy666]

first question:
could you tell me what the result should be for this whole table?

 

[michnance]

To make matters worse for completing my end goal the information provided to me includes omissions, errors, etc. Like the last address should have a drive or street or something on the end and other lines are missing city, state and/or zip. Its a mess. I'll have to gather all missing info line by line

 

[sandy666]

this is a titanic job!
I can sort it out a bit, but it will take some time, the rest belongs to you

especially if I don't know structure of addresses in US

 

[sandy666]

Hi RealEstate Agent

here is example of your excel file

there are many typos, shortcuts and non-existing things
you need to check for errors and correct it in Table1 (in PQ Query Editor not in the blue table in the sheet!!!)
eg. 5606 Oasis Pt Oviedo FL 32765 => Pt ? (I really don't know what Pt means ) so use Replace Value, type " Pt " (spacePtspace) and no quotes then word what it means, like St = Street
you can look for errors in filtered columns but all correction you need to do in PQ Editor Table1 column raw

sorry but No Pain, No Gain

After all correction call me here again or PM me (don't forget clean your inbox!)

have a nice day

btw. you can remove testing table. it is just for test how it looks after part of work

edit:
remember that PowerQuery is case sensitive so Street, street or STREET are different

 

[michnance]

Thank you SO much!!! I know this is a messy job. I've been miserable with the manual work so even though I had slim hopes of an excel solution, it was worth the shot! I will clean up. I can't thank you enough!!

ps: PT = Point

 

[michnance]

wait! the excel file you provided expired. will you send again just so I can take a look? THANK YOU!!