• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2066
  • Last Modified:

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
0
GravitaZ24
Asked:
GravitaZ24
  • 3
  • 2
2 Solutions
 
FlysterCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 PatelTraderCommented:
State =LEFT(RIGHT(G1,LEN(Q1)+3),2)
Zip    =TRIM(RIGHT(SUBSTITUTE(G1," ",REPT(" ",99)),99))
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now