Solved

# Parse Address in an Excel Cell

Posted on 2014-01-15
1,241 Views
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
0
Question by:GravitaZ24
• 3
• 2

LVL 22

Accepted Solution

Flyster earned 300 total points
ID: 39784458
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

LVL 8

Expert Comment

ID: 39784733
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 Comment

ID: 39785523
Thank you, Worked perfectly!
0

Author Comment

ID: 39785530
itjockey, sorry I requested they give you points for your help.
0

LVL 8

Expert Comment

ID: 39786051
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

LVL 8

Assisted Solution

itjockey earned 100 total points
ID: 39796004
State =LEFT(RIGHT(G1,LEN(Q1)+3),2)
Zip    =TRIM(RIGHT(SUBSTITUTE(G1," ",REPT(" ",99)),99))
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.