Separating data with spaces and putting in two different cells

My issue is complicated. I need to take data from a cell that has a space "cherry banana", and make it into two cells "cherry" and "banana". I want to duplicate my line on which the data resides, if possible.

What I have is a sheet like this:

IDs Data
1   banana
2   apple
3   cherry apple
4   cherry banana apple
5   apple

and I need to turn it into this:

IDs Data
1   banana
2   apple
3   cherry
3a  apple
4   cherry
4a  banana
4b  apple
5   apple

Any idea how I can do this?

If not, then how do I simply parse out my data in a cell to other cells using the space as the delimiter?

Thanks,
Multi

Originally Posted by multimediocrity

If not, then how do I simply parse out my data in a cell to other cells using the space as the delimiter?

Thanks,
Multi

Go to Data|Text to Columns.. Select Delimited.. click Next.. Select Space.. click Finish

Highlight the column with your data. Then click on DATA -> Text to Columns. When asked, select DELIMITED and then select the delimiter of SPACE...this should do it for you.

Hope that helps,

GWB

Thanks! I assume this is only for parsing out the data in the cell, not creating new rows, correct?

Wow, you guys are quick. Thanks!!

Correct.. =)

You can determine a destination for the delimited data to sparse out to....you can do this by clicking DESTINATION before you select FINISH...then identify where the data is to sparse out to...this is good for keeping the original cells intact with the original data, but creating a sparsed copy elsewhere - which doesnt have to be on the same rows

HTH,

GWB

adumbstudent,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.