Excel Help

[rdowns]

I'm setting up lists that will be uploaded into a database (comma delimited). The spreadsheet fields are name, address, city, state, zip, phone etc.

A colleague did a crap load of these but copied the city, state, zip into a single field.

Is there a way for me to convert that to the fields above without copy and pasting a million times?

 

[KC2]

So is it like:

City1, State1, Zip1
City2, State2, Zip2
City3, State3, Zip3

All of these are under Column A, but in separate rows? And they ARE separated by commas? Have you tried the Text to Columns feature?

As you may know Office 2003, 2007, Office 2008 for Mac are all a little different in their own regard, but the function is pretty much the same:

http://office.microsoft.com/en-us/excel/HA011498511033.aspx

 

[sushi]

If I understand what you want to do, you could set up a Macro or maybe a complex IF statement, to do this.

Basically parse the string (cell contents) into three parts. Then save each part in three different columns.

When done, copy and paste there they go by content only with no equations.

 

[Consultant]

General idea:

Copy the data from the field to text editor

Use Search and Replace in text editor to replace ',space' with 'tab'
or ',' with ',tab'

Paste back to spreadsheet.

 

[rdowns]

I tried text to colums but i get a message saying that cannot change part of merged cell.

 

[KC2]

You may want to give the other responses a try too, but if you stay with TTC...

It sounds like you might be selecting more data than you need to. Select just the data you want to split up. All the data should be in consistently formatted cells (no merged cells). So, for example, if I were to split this data here: http://www.federalreserve.gov/releases/h15/data/Business_day/H15_PRIME_NA.txt

I would select starting with the word DATE (where the table starts) down to whatever row I want. I copy/paste this into Excel. Highlight this same data (the data you want to breakdown), then select the TTC feature. Choose Delimited, then choose Comma as the Delimiter, and that's pretty much it. It then breaks that comma separated data into two separate rows.

Hope this helps some!

 

[sushi]

I tried text to colums but i get a message saying that cannot change part of merged cell.

Check out the string functions like Right, Left, etc.

Maybe you can chop off parts to get what you want saved into new cells.

I would probably parse from right to left because you know the zip code length, and you know the state length assuming that it is abbreviations. What's left is the city.

 

[rdowns]

Thanks for the suggestions. Going to try these.

 

[bartelby]

In the city column use:

=LEFT(A1,FIND(" ",A1)-1)


in the state column use:

=MID(MID(MID(SUBSTITUTE(A1," ","^",1),1,256),FIND("^",SUBSTITUTE(A1," ","^",1)),256),2,FIND(" ",MID(MID(SUBSTITUTE(A1," ","^",1),1,256),FIND("^",SUBSTITUTE(A1," ","^",1)),256))-2)

and in the zip use:

=RIGHT(A1,FIND(" ",A1)-1)

 

[reclusivemonkey]

I'm sure you've got enough to go on here, but I would save as a CSV and then import it again; it should allow you to separate the fields if they have commas in them.

 

[bartelby]

I'm sure you've got enough to go on here, but I would save as a CSV and then import it again; it should allow you to separate the fields if they have commas in them.

Ha! I some how missed that the stuff had commas in!

I spend most of my working day arseing about with huge datasets that people have messed up. By the end of the day I'm blind to commas...