# Parse Address in an Excel Cell

Hi. I need to separate an excel cell that has a full address Column "G" into separate cells, I'm having a difficult time figuring it out. I need separate cells for STREET ADDRESS, CITY, STATE, ZIP.

310 E Main St, 110 Charlottesville VA 22902

Into separate cells for
STREET = 310 E Main St
CITY = 110 Charlottesville
STATE = VA
ZIP = 22902

Book1.xlsx
###### Who is Participating?

Commented:
Here's the formulas used to get the results you were looking for:

STREET: =LEFT(G1,FIND(",",G1)-1)
CITY: =SUBSTITUTE((TRIM(MID(G1,LEN(N1)+2,LEN(G1)-LEN(N1)-10))),",","")
STATE: =LEFT(RIGHT(G1,8),2)
ZIP: =RIGHT(G1,5)

This works as long as your data is uniform.

Flyster
Book1.xlsx
0

This only suggestion ...if there is case of more then 5 number of pin

State=TRIM(RIGHT(SUBSTITUTE(G1," ",REPT(" ",99)),99))

Thanks
0

Author Commented:
Thank you, Worked perfectly!
0

Author Commented:
itjockey, sorry I requested they give you points for your help.
0

No I had just given suggestion and that is not for points.Mr.Flyster's solution is totally matched to what you are seeking.Actually I am not an expert I just see the questions and solutions. For my knowledge.

Thank You
0

State =LEFT(RIGHT(G1,LEN(Q1)+3),2)
Zip    =TRIM(RIGHT(SUBSTITUTE(G1," ",REPT(" ",99)),99))
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.