Hello,

I have a list of about 5000 cities with their associated state (seperated
with a coma). The list of cities are in colomn "A".

How can I automatically have the city copied into column "B" and the state
in column "C" without having to use the TEXT TO COLUMN option?

For the state, I can use "=RIGHT(A2,2)" so I'm ok with that. It's how do I
do the same for the city as every cities have different amount of characters
but are all located in front of a coma.

Thanks!!!

Assuming everything is in format of: city name, ST
State, as you said: =RIGHT(A2,2)
City would be: =LEFT(A2,LEN(A2)-4)
--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"One-Leg" wrote:

Hello,

I have a list of about 5000 cities with their associated state (seperated
with a coma). The list of cities are in colomn "A".

How can I automatically have the city copied into column "B" and the state
in column "C" without having to use the TEXT TO COLUMN option?

For the state, I can use "=RIGHT(A2,2)" so I'm ok with that. It's how do I
do the same for the city as every cities have different amount of characters
but are all located in front of a coma.

Thanks!!!

Thank you!!! It worked like a charm...

"John C" wrote:

Assuming everything is in format of: city name, ST
State, as you said: =RIGHT(A2,2)
City would be: =LEFT(A2,LEN(A2)-4)
--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"One-Leg" wrote:

Hello,

I have a list of about 5000 cities with their associated state (seperated
with a coma). The list of cities are in colomn "A".

How can I automatically have the city copied into column "B" and the state
in column "C" without having to use the TEXT TO COLUMN option?

For the state, I can use "=RIGHT(A2,2)" so I'm ok with that. It's how do I
do the same for the city as every cities have different amount of characters
but are all located in front of a coma.

Thanks!!!

Hi,

Question - why not use the text to columns wizard?

--
Thanks,
Shane Devenshire


"One-Leg" wrote:

Hello,

I have a list of about 5000 cities with their associated state (seperated
with a coma). The list of cities are in colomn "A".

How can I automatically have the city copied into column "B" and the state
in column "C" without having to use the TEXT TO COLUMN option?

For the state, I can use "=RIGHT(A2,2)" so I'm ok with that. It's how do I
do the same for the city as every cities have different amount of characters
but are all located in front of a coma.

Thanks!!!