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
GravitaZ24Asked:
Who is Participating?
 
FlysterConnect With a Mentor 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
 
Naresh PatelTraderCommented:
This only suggestion ...if there is case of more then 5 number of pin

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


Thanks
0
 
GravitaZ24Author Commented:
Thank you, Worked perfectly!
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
GravitaZ24Author Commented:
itjockey, sorry I requested they give you points for your help.
0
 
Naresh PatelTraderCommented:
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
 
Naresh PatelConnect With a Mentor TraderCommented:
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.

All Courses

From novice to tech pro — start learning today.