Separating data in an Excel cell into multiple cells

[jbedwe02]

Hi all,
I have no clue how to slove this problem & am desperate for an answer:

I'm creating a spreadsheet and need to now how to seperate data within a single cell into multiple cells.

The info is currently stored in a single cell using line breaks and I want to seperate the info in each line break into specific cells.

Example:

red
blue
green

(above is in one cell with line breaks) into:


red

blue

green

(above is transformed into three different cells).


If anyone can help that would be great, very very much appreciated!!
Josh

[yashgvt]

Hi all,
I have no clue how to slove this problem & am desperate for an answer:

I'm creating a spreadsheet and need to now how to seperate data within a single cell into multiple cells.

The info is currently stored in a single cell using line breaks and I want to seperate the info in each line break into specific cells.

Example:

red
blue
green

(above is in one cell with line breaks) into:


red

blue

green

(above is transformed into three different cells).


If anyone can help that would be great, very very much appreciated!!
Josh

Hi Josh,

AFAIK there is no function or procedure to do this, however if the data is large and is in one cell, then copy the contents of cell and paste it in a word document where you can convert text to table. Then copy the table and paste it in excel.

I know this is not exactly what you expected. just i am sharing my thoughts.

Regards,

[jbedwe02]

Thanks for the info Yashgvt, its a toughy and I didnt think there was a nice easy way to do this. The problem is I have heaps of cells that need doing and I cant spend the time doing them manually.
If there is no exact way I might go back to the start and work out a way not to have to use line breaks and instead just use whole cells for differing points of info.

Is there anyway we can suggest to Microsoft that for their next edition can they include this function?!?! lol, im that desperate...

Cheers

[pbmods]

Changed thread title to better describe the problem.

[jjsmith]

Hi,

Theres two ways you can do this, either make complex formulas as described here:

http://www.informit.com/articles/article.aspx?p=174320&seqNum=3

OR...

Use the Excel Wizard (in Excel 2003 its called Text to Columns under the Data menu) as described here:

PS: There's no need to ask Microsoft to include such a feature as its apparently been there years, but maybe they could either rename it to something more descriptive or list it in their help file as I wasted ages trying to use method 1 before stumbling across method 2!! ;)

JJ

[jjsmith]

whoops forgot to link to where I found out about method two..

http://www.uwec.edu/help/Excel03/ws-split.htm

Thanks UWEC :)

Note:
I found this info in google searching for "separating data in Excel cell" which nearly matches your post heading exactly, which is also how I noticed your post and felt sorry for you as I myself have just spent ages trying to figure this out!!!