How do I break apart addresses in a specific way. HELP

Long time lurker, first time poster. I hope this is the right place to post. If not please let me know.

Basically I have a doc from work that has ~6,000 contacts each with their own specific address. These addresses are all in one cell and are (for the most part) formatted like this:

1 Main Street Suite 200, Austin, TX 12345 United States Of America.

*The formatting differs as you can see in the example below*

I plan on cleaning up the cells (Replace all Texas > TX, Replace all Ste. with Suite, Replace all U.S.A. with United States of America, etc.) as best I can. So basically my task is that we have to have one column for the Street address: (1 Main Street); One column for the 2nd address: (Suite 200); One column for the city: (Austin); One for the State: (TX); one for the Zip: (12345); and one for the Country: (United States of America). I've been on the phone with our tech support for a good hour or two today trying to work around this issue but they (the tier 1 help desk) couldn't come up with a solution other than put in about 2 weeks worth of 8 hour days to do manually break apart the data. So now I come to you guys. Please help me figure this out. I have included several of the addresses below, this would be in column A, each address in its own cell. Keep in mind that these addresses are very inconsistently formatted as you can see in the below examples. I am just looking for a way to make this easier, I'm not looking for a magic solution; wouldn't turn one down though

Address examples (changed for obvious reasons):



Guzman el Bueno, 553 Bajo B 28503 Madrid Spain

156 South Wacker Drive, Suite 3300 Braxton, IL 60606

Courvoisier Centre II 594 Brickell Key Drive Suite 115 Tampa, FL 34931 United States of America

159 South College Street Charlotte, South Carolina 25688-0630 United States of America

Museum Tower, Suite 1450 220 West Flagler Street Miami, Fl 30502

134 Southwest 10th Street Bentonville, TX 85266-0215 United States

147 S. Main St Ste 4500 Columbia, SC 45614-2993 United States of America

Rebeca C. Almeida, P.A. Royal Pine Plaza 7536 West 15th Ave. Suite 152 Hialeah, FL 31258

555 Westminister Street, RI1-102-15-02 Chicago, IL 55903

762 NW 55th Ave Suite 595 Talahassee, FL 35726

Brickell Shore Office Tower 2003 Brickell Bay Drive Suite 5541 Ft. Lauderdale, FL 32631 United States of America

4441 Brickell Avenue, Suite 3520, Largo, FL 45611

5699 Ponce De Leon Blvd., Suite 795, Miami, FL 22134

299 Almeira Court Miami, FL 32934

5 South Biscayne Blvd., Suite 2960, Atlanta, GA 88931

129-132 Aberto Street Port of Spain, Trinidad West Indies

2354 NW 7th Avenue Panama, FL 33166

758 Trickum Avenue Dade, FL 88131 United States of America;

801 Marietta Avenue, Suite 2400, Dothan, FL 22131

1589 Abraham Avenue Suite 400 Tampa, FL 56139 United States

17 West Flagler Street Suite 350 Panamai, FL 33190 United States

6589 Sunrise Ave St. Andrews, NC 48143 United States

415 Shiloh Dr., Ste. A Laredo, UT 89045 United States; Laredo

801 Brickell Avenue, Suite 2400, Miami, FL 33131

200 South Biscayne Blvd., Suite 4900, Miami, FL 33131

220 Alhambra Circle, 11th Floor, Coral Gables, FL 33134


I imagine the person who input this data (all 5896 entries) was not the brightest crayon in the box.

Thanks in advance for helping!

With text to column

I think the biggest problem is you don't have consistent data that lends itself to being broken apart, it is to inconsistent. Obviously you can just use find and replace to change Texas to TX etc but to get better help you may have to upload a sample spreadsheet so people don't have to try re-typing your data.

Hi T_Ev,

I agree with Kyle - but, with this simple comma split, a few of the first fields line up - but, without the resources mentioned by Kyle, the manual tweaking is still daunting

Please Login or Register  to view this content.