City, ST ZIP need to separate into 3 fields
 

Hi All ~

Need to separate this kind of data into 3 fields

Los Angeles, CA 90038

I found this bit of code which ALMOST does what i need it to

For the city, use
=LEFT(A1,FIND(",",A1)-1)
For the state, use
=RIGHT(A1,LEN(A1)-FIND(",",A1)-1)


but it comes back with the State and Zip still in the same field and
there is no coma between I can use to select. Is there a way to get it
to grab only numbers?

THANKS!
JB


--
JB1981
------------------------------------------------------------------------
JB1981's Profile: http://www.excelforum.com/member.php...o&userid=34807
View this thread: http://www.excelforum.com/showthread...hreadid=545660

City, ST ZIP need to separate into 3 fields
 

For the State:

=MID(A1,FIND(",",A1,1)+2,2)

For the Zip (assuming it is always 5 digits)

=RIGHT(A1,5)

HTH,
Elkar


"JB1981" wrote:


Hi All ~

Need to separate this kind of data into 3 fields

Los Angeles, CA 90038

I found this bit of code which ALMOST does what i need it to

For the city, use
=LEFT(A1,FIND(",",A1)-1)
For the state, use
=RIGHT(A1,LEN(A1)-FIND(",",A1)-1)


but it comes back with the State and Zip still in the same field and
there is no coma between I can use to select. Is there a way to get it
to grab only numbers?

THANKS!
JB


--
JB1981
------------------------------------------------------------------------
JB1981's Profile: http://www.excelforum.com/member.php...o&userid=34807
View this thread: http://www.excelforum.com/showthread...hreadid=545660

City, ST ZIP need to separate into 3 fields
 

JB, checkout http://www.add-ins.com/address_assistant.htm

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel


"JB1981" wrote in
message ...

Hi All ~

Need to separate this kind of data into 3 fields

Los Angeles, CA 90038

I found this bit of code which ALMOST does what i need it to

For the city, use
=LEFT(A1,FIND(",",A1)-1)
For the state, use
=RIGHT(A1,LEN(A1)-FIND(",",A1)-1)


but it comes back with the State and Zip still in the same field and
there is no coma between I can use to select. Is there a way to get it
to grab only numbers?

THANKS!
JB


--
JB1981
------------------------------------------------------------------------
JB1981's Profile:
http://www.excelforum.com/member.php...o&userid=34807
View this thread: http://www.excelforum.com/showthread...hreadid=545660