cell disection

[David Van Horn]

Ok...I need to be able to remove part of a cell and post
it in a differnt cell...

i have a cell with the city, state zipcode in it and i
need to be able to have excel pull out just the city and
place in one cell pull out the state and put in a
different cell and then pull out the zip and put in a
third cell ... how can it be done??

 

[Bernard V Liengme]

What is between the city name and the state name? A space, a comma.....?
Have you experimented with Data|Text to Columns?
Bernard

 

[Harald Staff]

Hi David

At its best: Select the cell(s), go menu Data > Text to columns. Voila.

Problem:
Denver = 1 cell
New York City = 3 cells.

At its worst: I have a cake and I need 3 eggs, a cup of butter and a pound of sugar.

 

[James S]

Hi David,

Assuming that all the data is exactly like this:

City, State ZipCode
(CITY comma space STATE space ZIPCODE)
* And the zip code is five digits.

You can use the following formulas:

CITY (Column B)
=LEFT(A2,(FIND(",",A2))-1)

STATE (Column C)
=LEFT(TRIM(RIGHT(A2,(LEN(A2)-FIND(",",A2)))),2)

ZIPCODE (Column D)
=RIGHT(TRIM(A2),5)

RESULTS
A B C D
1 City, State ZipCode City State ZipCode
2 Seattle, WA 98102 Seattle WA 98102

Also, it may be helpful to perform Text To Columns as some
other people have mentioned. If you have to use formulas
though, this should do it.

Hope that helps.

Regards,
James S