Address list

[sajohnson05]

I have a list of over 1200 addresses. Some of them include the full address city state zip, some are missing the zip and some are just a street address. I am wanting to create 4 columns but i can not figure the best way to do this since the data that i am currently using is so different. Any help would be great.

7325 Medical Center Drive Suites 104 and 105 West Hills, CA 91307
2191 E. Johnson Ave. Pensacola, Fl.
12141 Richmond Avenue

Address City State Zip

 

[kweaver]

I assume there's no chance you can separate the address from the city with a comma?!?!

If your data is in column A, starting in row 2,
I believe I can get the zip codes using this:

=IF(ISNUMBER(1*RIGHT(A2,5)),RIGHT(A2,5),"")

I then think I can get the state using this:

=IF(EXACT(RIGHT(IF(ISNUMBER(1*RIGHT(A2,5)),LEFT(A2,LEN(A2)-6),RIGHT(A2,2)),2),UPPER(RIGHT(IF(ISNUMBER(1*RIGHT(A2,5)),LEFT(A2,LEN(A2)-6),RIGHT(A2,2)),2))),RIGHT(IF(ISNUMBER(1*RIGHT(A2,5)),LEFT(A2,LEN(A2)-6),RIGHT(A2,2)),2),"")

Phew!!

 

[sajohnson05]

if it would make things easier i could go through all 1200 lines and add the comma

 

[kweaver]

If you could have a comma after the address and before the city, I think this will get the city:

=IFERROR(TRIM(MID(A2,FIND("^",SUBSTITUTE(A2,",","^",1))+1,FIND("^",SUBSTITUTE(A2,",","^",2))-FIND("^",SUBSTITUTE(A2,",","^",1))-1)),"")

 

[kweaver]

Again, if you can have the comma delimiter, this will give you the address:

=IF(1=FIND(C2,A2),A2,LEFT(A2,FIND(C2,A2)-3))

Using:

7325 Medical Center Drive Suites 104 and 105, West Hills, CA 91307

2191 E. Johnson Ave., Pensacola, FL

12141 Richmond Avenue

100 Main Street, San Diego, CA 92122

I cannot think of a way to insert the comma after the address and before the city. SIGH

 

[sajohnson05]

This has help a lot. I know going through all these lines wont be fun but sometimes you got to do the dirty work to clean data.