How to separate bad data entry addresses from one cell to multiple cells

[steve5740]

Examples:
GREENBRIAR MALL 1712 RING RD CHESAPEAKE VA 23320
101 N. HIGHWAY 274 HWY 175 @ HWY 274 KEMP TX 75143-0000
229 NORTH SEVEN POINTS RT. #8 - BOX 925 SEVEN POINTS TX 75143-0000
3223 LEMMON AVE #100 DALLAS TX 75204
8001 WESTERN HILLS BLVD FORT WORTH TX 76108

Hello -

I have a spreadsheet with addresses in one column, and need to separate the data by address, city, state and zip code. Because the data is different lengths, including 2 word cities, it has been difficult to develop a successful formula and text to column does not help. Any suggestions would be helpful.

 

[steve5740]

Examples:
GREENBRIAR MALL 1712 RING RD CHESAPEAKE VA 23320
101 N. HIGHWAY 274 HWY 175 @ HWY 274 KEMP TX 75143-0000
229 NORTH SEVEN POINTS RT. #8 - BOX 925 SEVEN POINTS TX 75143-0000
3223 LEMMON AVE #100 DALLAS TX 75204
8001 WESTERN HILLS BLVD FORT WORTH TX 76108

Hello -

I have a spreadsheet with addresses in one column, and need to separate the data by address, city, state and zip code. Because the data is different lengths, including 2 word cities, it has been difficult to develop a successful formula and text to column does not help. Any suggestions would be helpful.

I have tried the following but it does not help with 2 word cities. How do I adjust the formula?
8001 WESTERN HILLS BLVD FORT WORTH TX 76108
Cell B2 =TRIM(SUBSTITUTE(A2,D2&" "&E2,""))
Cell C2 =TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A2," "&E2,"")," ",REPT(" ",50)),50))
Cell D2 =TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",50)),99))