Excel - generic way to split Address from one column into multiple columns.

Assume Address data is in Column E and has data like "123 Any ST Anytown NY 12345"

First Zipcode match for K2 = Right(E2,5)
Or Combined to match all in one shot -
=IF(COUNTIF(E2,"*-*"),RIGHT(E2,10),RIGHT(E2,5))

Zip Code matching - figure out if it has a 9 digit code instead of 5
=IF(COUNTIF(K2,"*-*"),RIGHT(E2,10),K2)


Find position of last space in string

=FIND("?",SUBSTITUTE(E2," ","?",LEN(E2)-LEN(SUBSTITUTE(E2," ",""))))

From <https://www.bing.com/search?q=excel+find+position+of+last+space+in+string&qs=UT&pq=excel+find+position+of+last+space&sc=10-33&cvid=385C46DC879C40EDBC2AAA61EA4896DF&FORM=QBRE&sp=1&ghc=1&lq=0>

Find state:

=MID(E2,FIND("?",SUBSTITUTE(E2," ","?",LEN(E2)-LEN(SUBSTITUTE(E2," ",""))))-2,2)



Find 2nd to last space:

=FIND("/",SUBSTITUTE(E2," ","/", LEN(E2)- LEN(SUBSTITUTE(E2," ",""))-1))

From <https://excelnotes.com/how-to-locate-the-last-space-in-the-text-string/>

Third to last space

=FIND("/",SUBSTITUTE(E2," ","/",LEN(E2)-LEN(SUBSTITUTE(E2," ",""))-2))

From <https://excelnotes.com/how-to-locate-the-last-space-in-the-text-string/>


=MID(E2,
FIND("/",SUBSTITUTE(E2," ","/", LEN(E2)- LEN(SUBSTITUTE(E2," ",""))-1)),FIND("/",SUBSTITUTE(E2," ","/",LEN(E2)-LEN(SUBSTITUTE(E2," ",""))-2))-FIND("/",SUBSTITUTE(E2," ","/", LEN(E2)- LEN(SUBSTITUTE(E2," ",""))-1)))


Find City

I2 = Pos 2nd to last space = =FIND("/",SUBSTITUTE(E2," ","/", LEN(E2)- LEN(SUBSTITUTE(E2," ",""))-1))
J2 = Pos3rdtolastspace = =FIND("/",SUBSTITUTE(E2," ","/",LEN(E2)-LEN(SUBSTITUTE(E2," ",""))-2))
=MID(E2,J2,I2-J2)



Only about 20 didn't match the city

Street Address formula (in column H)

=MID(E2,1,I2)