PLEASE SHOW ME HOW TO Separate full address INTO COLUMS ADDRESS, CITY, STATE, ZIP

[keepgoing]

I NEED TO SEPARATE FULL ADDRESS IN DIFFERENT COLUMS OR COMMAS:
Example:
THE WAY I HAVE THE DATA IS LIKE THIS:

THIS IS THE WAY HAVE THE LARGE DATA ADDRESS CITY STATE ZIP 9737 NW 41 ST SUITE 182 DORAL, FL 33178 19737 Carabana st. Fort Myers, FL 33901 <tbody> </tbody> 4341 23RD AVE SW NAPLES, FL 34156 1151 KELVIN GROVE PLACE, JACKSONVILLE, FL 32225 <tbody> </tbody> 14925 sw 42 pl miami, fl. 33188 <tbody> </tbody>

<tbody>
</tbody>

THANK YOU,

 

[oldbrewer]

state and zip are easy
address and city are impossible because Doral is one word, Fort Myers is 2 , suite 182 or similar is unpredictable, kelvin grove place is 3 words, it could be 2 or 1 if you have a lookup list of every town in USA it would help

I am uk so don't know zips but is FL 33188 always

14925 sw 42 pl miami <tbody> </tbody>

<tbody>
</tbody>

 

[s_woodbury]

Keepgoing,

If there was consistency with the punctuation you could use them to separate the addresses but as oldbrewer mentioned there isn't any predictability. If there is a comma at the end of each city you can identify the position of it in the string. you can also identify the position of spaces and use that to pull data into a new cell. If you did that it would only partially solve your problem for Cities that have more than one word (Ft Myers)