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]

Sure, here is easier version. You need correct ca. 200 records marked yellow.
addresses