Get part of the Address

[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

 

[Frank Kabel]

Hi
try using 'Data - Text to columns' for this

 

[daniels012]

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

 

[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

 

[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

 

[daniels012]

Thank you for your quick reply! I think these will work!

Michael

 

[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.
:
:
: ---
: Message posted
:

 

[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