Separate Zip Code from Address

[geddes_3]

I have a spreadsheet containing thousands of addresses, and I need to separate the zip code from the address. Each address is in its own cell, and the only divider is spaces (sometimes two spaces). I attached an example of what some entries look like. Is it possible single out the zip code? addresslist example.xlsx

[jemmers]

I'd do a find and replace and just remove the Country all together so the data has the zip code at the end.

After that, I would use the =RIGHT(cell,5) formula in the cell right next to the address listing to get the zip code separated out.

[FDibbins]

Could you provide a some more realistic examples (dummy) to work with please, and where is this data coming from?

[geddes_3]

Thanks for the suggestion jemmers. Two problems with that approach are there are hundreds of countries, and some zips are formatted "zip+4." Here's a more realistic example with dummy addresses. addresslist example2.xlsx The list comes from internal software without the capability of separating the address into different columns.

I could also accomplish what I need to by separating the state and country

[FDibbins]

This works for most, but does not work if there is more than 1 word (space) after the number
=MID(TRIM(A2),SEARCH("xx",SUBSTITUTE(TRIM(A2)," ","xx",LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))-IF(ISNUMBER(RIGHT(A2,3)*1),0,1)),1)+1,5+IF(ISNUMBER(SEARCH("-",A2,1)),5,0))

[geddes_3]

Thanks FDibbins. That makes my data much more manageable Can the country and state be separated as well using something similar?

[FDibbins]

This will pull the country from most of the entries (it pulls the last word after the last space)...
=IF(ISNUMBER(RIGHT(A2,3)*1),"",MID(SUBSTITUTE(TRIM(A2)," ","xx",LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))),SEARCH("xx",SUBSTITUTE(TRIM(A2)," ","xx",LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))),1)+2,99))

[FDibbins]

If you have that very 1st formula I gave you in column C (adjust as needed), you can use this to pull out the state (again, it will not work with all)...
=MID(A2,SEARCH(C2,A2,1)-3,2)

[geddes_3]

Thank you! It may not be a perfect solution, but it's probably the best one for managing this sloppy data

[FDibbins]

Happy to help and thanks for the feedback

[diegoabril]

FDibbins, What does the Search("xx" does exactly and what are the substitutes for?

[FDibbins]

I use that as a "place holder". It is pretty much a unique text string that is easy to find. To keep the explaination simple, I will ignore those entries that did not have the zip code at the end.

I counted the lenth of the string using =len(). Then I used substitute() to remove the spaces, and thenb count the length of that modified string. That tells me how many spaces there are, and I replace the last space with "xx" - so I know where that space is. The seach then looks for the "xx", to find where the numbers start

Make sense? Clear as mud?

[Haseeb Avarakkan]

May be this also,

B2: for ZIP Codes,

=TRIM(RIGHT(SUBSTITUTE(LEFT(A2,MATCH(10,INDEX(MID(A2,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A2))),1)+0,0)))," ",REPT(" ",20)),20))

C2: for States

=TRIM(RIGHT(SUBSTITUTE(LEFT(A2,FIND(B2,A2)-2)," ",REPT(" ",10)),10))

D2: for Country

=TRIM(MID(A2,FIND(B2,A2)+LEN(B2),999))

Then B2:D2 & copy down.