Separating Suburb and ZIP from addresses

Try this... Zip:
=IFERROR(--RIGHT(B3,4),"")

Adress:
=IF(ISERROR(--RIGHT(B3,4)),B3,LEFT(TRIM(SUBSTITUTE(B3,D16,"")),FIND("|",SUBSTITUTE(TRIM(SUBSTITUTE(B3,D16,""))," ","|",LEN(TRIM(SUBSTITUTE(B3,D16,"")))-LEN(SUBSTITUTE(TRIM(SUBSTITUTE(B3,D16,""))," ",""))))-1))

Suburb:
=TRIM(SUBSTITUTE(SUBSTITUTE(B3,D16,""),B16,""))

HOWEVER, this will NOT work if the suburb has two words.... How often can that occur?

Hi Glenn

Thanks a lot for your help.

To answer your question first, there are quite a few addresses that don't have a ZIP in them. So that makes them two consecutive words without any numbers.

Also, I am having a hard time understanding what's the best way to run this formula, as unlike the sample I provided, the actual sheet has a few thousand rows of data. So I was wondering if there is a way I could use a formula where I create a second Address column (besides the original one) along with suburb and zip columns and then use the formula in the second address column and it would automatically split up the address and fill up the corresponding, address, suburb and zip columns.

Please see the attached spreadsheet to better understand what I mean

I am sorry if this is too much to ask for. Just trying to figure out a way to run your formula in the best possible way.

Thanks again

I have applied the previous solution to the sheet. It is NOT what you want yet....

Am I correct in assuming that for 6/46 Hobill Ave Manukau Auckland 2104 you want it split as:

6/46 Hobill Ave
Manukau Auckland
2104

1) If so, I need you to provide me with as complete a list as possible of allowable address endings (Rd, St, Pl, etc)

2) If not, then manually enter your expected results in columns C, D & E; and do 1) as well....

Originally Posted by Glenn Kennedy

Am I correct in assuming that for 6/46 Hobill Ave Manukau Auckland 2104 you want it split as:

6/46 Hobill Ave
Manukau Auckland
2104

This would even be better

1) If so, I need you to provide me with as complete a list as possible of allowable address endings (Rd, St, Pl, etc)

I went through around 650 records and these are what appeared to be the ending of addresses

Ave, St, Pl, Rd, RD, Dve, Cre, Ct, Way, Rse, Qy

My expected results are in the spreadsheet attached. I actually need to break down the address into suburb, city and ZIP

Many thanks

Hi. Apologies for the delay!! I couldn't get logged into the forum, for some reason.

I had just finished this, when I noticed that you had changed your requirement. I suspect that what you are asking for is impossible. If either the suburb, or the city can have more than one word - unless you have an exhaustive list of both, then there is no way that Excel can understand the rule by which to separate them. I have, however, completed your previous request. Can you live with this??

C2=IF(ISERROR(--RIGHT(B2,4)),B2,TRIM(LEFT(SUBSTITUTE(B2," "," "),SMALL(INDEX(SEARCH({"Ave ","St ","Pl ","Rd ","Dve ","Cre ","Ct ","Way ","Rse ","Qy "},SUBSTITUTE(B2," "," ")&"Ave St Pl Rd Dve Cre Ct Way Rse Qy ")+3,0),SUM(INDEX((SEARCH({"Ave ","St ","Pl ","Rd ","Dve ","Cre ","Ct ","Way ","Rse ","Qy "},SUBSTITUTE(B2," "," ")&"Ave St Pl Rd Dve Cre Ct Way Rse Qy ")<=LEN(SUBSTITUTE(B2," "," ")))*1,0))))))

Please Login or Register  to view this content.

D2=TRIM(RIGHT(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,H2,""),K2,""),J2,""))," ",REPT(" ",100)),100))

Please Login or Register  to view this content.

E2=TRIM(RIGHT(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(B2,H2,""),K2,""))," ",REPT(" ",100)),100))

Please Login or Register  to view this content.

F2=TRIM(RIGHT(SUBSTITUTE(TRIM(SUBSTITUTE(B2,H2,""))," ",REPT(" ",100)),100))

Please Login or Register  to view this content.

You're welcome...