CODEWITHSUNDEEP
excelworksheet-function
thebradnet
thebradnet

Reputation: 125

Address Split Formula

Any idea on how I can separate an address in Excel. Currently I have about 300 addresses that are in the flowing format:

1234 Address City, AL 55555

I have looked online and I have found out how to separate the city and zip code. Now I am having a problem getting the City and Address in a separate cell. Any ideas of how I can do this?

Upvotes: 0

Views: 2701

Answers (1)

Lance Roberts
Lance Roberts

Reputation: 22842

If it's always in that exact format, then if the city has only one word (with unlimited spaces in the address):

Addr:  =LEFT(A5,
        FIND("~",SUBSTITUTE(A5," ","~",FIND(",",A5)-1-LEN(SUBSTITUTE(LEFT(A5,FIND(",",A5)-1)," ","")))))
City:  =MID(A5,
        FIND("~",SUBSTITUTE(A5," ","~",FIND(",",A5)-1-LEN(SUBSTITUTE(LEFT(A5, FIND(",",A5)-1)," ",""))))+1,
        FIND(",",A5) - 1 - FIND("~",SUBSTITUTE(A5," ","~",FIND(",",A5)-1-LEN(SUBSTITUTE(LEFT(A5, FIND(",",A5)-1)," ","")))))
State: =MID(A1,FIND(",",A1)+2,2)
Zip:   =RIGHT(A1,5)

Upvotes: 2

Related Questions