I need Help to parse address city state zip and country if applicable for all fields

Can anyone help me to write a macro to parse the following format below I need to have the address city state zip and country if applicable separated in columns

514 N Bth St HOLLIS OK 73550 us
546 HIGHLAND AVE RIDGEWOOD NJ 7450 us
4610 BROWNDALE AVE MINNEAPOLIS MN 55424 us
5167 HWY 70 W STE 40 MOREHEAD CITY NC 28557 us
600 Delzan PI lEXINGTON KY 40503 us
4015 S 9TH ST COLUMBUS NE 68601 us
2 PERIMETER PARKS STE 100W BIRMINGHAM AL 35243 us
2612 FM 328 HUNTINGTON TX 75949 us
1400 S VANDEVENTER AVE ST. LOUIS MO 63110 us
1700 Boardwalk WILDWOOD NJ 8260 us
15543 VALERIO ST VAN NUYS CA 91406 us
310 Highway 9 WIMBLEDON NO 58492 us
15 HIGH RIDGE RD WEST HARTFORD CT 6117 us
1102 Maple Street SANTA MONICA CA 90405 us
11634 CHAPEL RD CLIFTON VA 20124 us
150 COOLIDGE AVE ENGLEWOOD NJ 7631 us
6565 E WASHINGTON BLVD LOS ANGELES CA 90040 us
6565 E WASHINGTON BLVD LOS ANGELES CA 90040 us
6565 E WASHINGTON BLVD LOS ANGELES CA 90040 us
6565 E WASHINGTON BLVD LOS ANGELES CA 90040 us
8312 NW 30TH TER MIAMI FL 33122 us
8312 NW 30TH TER MIAMI FL 33122 us
3700 N EVERBROOK LN MUNCIE IN 47304 us
1206 POLARIS Cf FORKED RIVER NJ 8731 us
109 PLUMWOOD RD DAYTON OH 45409 us
922 AMSTERDAM DR COLORADO SPRINGS CO 80907 us
984 OLD HIGHWAY 9 CLINTON AR 72031 us
984 OLD HIGHWAY 9 CLINTON AR 72031 us
4139 W VENUS WAY CHANDLER AZ 85226 us
4139 W VENUS WAY CHANDLER AZ 85226 us
95 Washington Street PEMBROKE MA 2359 us
1233 20TH ST NW STE 800 WASHINGTON DC 20036 us
15410 RIVER RD GERMANTOWN MD 20874 us
7656 E WOOD DH SCOTISDALE AZ 85260 us
7656 E WOOD DR SCOHSDALE AZ 85260 us
7656 E WOOD DR SCOTTSDALE AZ 85260 us
300 S RIVERSIDE PLZ STE 1900 CHICAGO ll. 60606 us
2900 BRISTOL ST STE F201 COSTA MESA CA 92626 us
48 MONROE TPKE TRUMBULL CT 6611 us
3037 W WOODS EDGE DR BLOOMSBURG PA 17815 us
2 JERICHO PLZ JERICHO NY 11753 us
777 LARKFIELD RD STE 114 COMMACK NY 11725 us
990 Iris Drive SW Ste 302 CONYERS GA 30094 us
1300 W WASHINGTON ST FL3 PHOENIX AZ 85007 us
49 WIRELESS BLVD STE 180 HAUPPAUGE NV 11788 us
10101 Ridgeland Ave CHICAGO RIDGE ll 60415 us
100 WHITE CLAY CENTER DR STE 1 NEWARK DE 19711 us

Not the best a leaves errors but may help some? I'm sure someone will come up with a better way.

Please Login or Register  to view this content.

first and foremost thank you for your help. I tried putting in macro and running it immediately I received a runtime error @ Range("C" & i).Value = Left(Range("A" & i), Len(Range("A" & i)) - 11)
and the macro did not return even one result any thoughts?

What was the runtime error? I ran it based on your above sample by placing it in Column A. It is not perfect by any means, but I didn't receive an error.

addresses.xlsx

Run time error 5 invalid procedure call or argument I will upload list of addresses

You're right I only tested on your sample. But when I tested on these it doesn't work. Perhaps someone else can offer a different strategy.