Get part of the Address

D

daniels012

I currently have the address City State and Zip all in one cell.
What is the easiest way using code to separate City, State Zip into
different cells?

Thank You
Michae
 
D

daniels012

This did not work because it uses a formula that gets the data from th
previous sheet
 
D

daniels012

really what I want is the City and state. So I can copy it to anothe
form.
Then I think use the mid formula to get the State.

I just don't know how to write these in code.

Or I can use a formula hidden somewhere on my form, then just copy it!

Any help is greatly appreciated!

Thank You,
Michae
 
E

ElsiePOA

Here's one way:

Assuming your data is in A1

In any blank cell enter =LEFT(A1,FIND(" ",A1)-2). That will give yo
the city. (There has to be a space between the qoutation marks). Thi
assumes there is a comma after the city. If not, change the "-2" i
the formula to "-1"

In the next cell, enter =MID(A1,FIND(" ",A1)+1,2). That will give yo
the State, assuming it is the 2 letter abbreviation
 
L

Lady Layla

What if they have San Fransisco or San Jose or New York as a city?


: Here's one way:
:
: Assuming your data is in A1
:
: In any blank cell enter =LEFT(A1,FIND(" ",A1)-2). That will give you
: the city. (There has to be a space between the qoutation marks). This
: assumes there is a comma after the city. If not, change the "-2" in
: the formula to "-1"
:
: In the next cell, enter =MID(A1,FIND(" ",A1)+1,2). That will give you
: the State, assuming it is the 2 letter abbreviation.
:
:
: ---
:
:
 
D

daniels012

These seemed to work! Unless I'm missing something

=MID(B7,1,FIND(",",B7,1)-1)
Gives me the City


=RIGHT(MID(B7,1,FIND(",",B7,1)+4),3)
Gives me the Stat
 
Top