How to separate data in excel 2007 ?

Question

I have the following lines

SAN LEANDRO,CA 94578 USA

How do I make it so that "San Leandro" "CA" "94578""USA" all show up in their own columns

Answer 1

Copy and past the data into a notepad document. Then open the notepad file in excel as a csv. It will ask you what you want to set as the columns. You can use a comma or a space or both so that everytime it come to a comma or space it will move the data into a new column.

Answer 2

Open Excel 2007 and click on the "?" at the top right for help. Search for "space delimited" and take a look at the video that they have there. Also search for "comma delimited" and look at what they have there. Save a copy of your data as a txt file. Import it into Excel as a delimited text file. I know that this is a mish mash of suggestions but you have a mixed file. Careful use of the various delimitation options in Excel should allow you to get most of your data in the appropriate columns. Good luck.

Answer 3

This is from 2003 as opposed to 2007 - not so familliar with 07 but most things are the same



Highlight the column



Go to Data, Text to columns (this may be on the data tab)



Choose comma as a delimitter



This should break down the data every time it finds a comma in this list

Answer 4

I don't like any of the answers given to you so far. The last one would kind of work but you have the issue where a city could be 1 or 2 words thus the space delimiting will fail. Here is my simple take by start on from the back:

First equation: =Left([cell of the address]), 3) - this should pull out the "USA"

Second equation: =Right(Left([cell of the address],8),5) - this should pull out the Zip.

you get the idea from there.

Answer 5

Just type each into it's own column. I would have a header row indicating City, State, Zip & Country so you can sort the data.