Extracting Comma Seperated data into individual Cells

Again, I must turn to the Excel Power Users with another dilemna. In the attached XLS I have 4 columns of data. My problem is extracting the data in columns B, C & D. I need to extract each color type to it's own individual cell. Example: I need 131A-Dark Gray placed in cell A1, 133-Blue placed in cell A2, 451-Burgundy placed in A3, etc, etc. The Data from columns C & D would also be placed in it's own row in the same way. Also as you can see each cell doesn't contain the same number of entries. This is for a project that is almost complete except for this data. I have tried to obtain this data for 3 days now.

Is there a way with a formula I can extract the individual color options for each item into a new sheet or am I going to have to do these entries manually, Yikes, I hope not I have 275 items. Someone suggested a Pivot Table and I have played with that for a whole day without any success. I feel like an idiot!!!!! I know that someone out there must know something

I now turn this over to all the brains.

Rich

Here is some additional information that may help. Each one of the color options in the cell is seperated by a comma so is there a way to get the data to the left and between the commas?

I couldn't get to look at your spreadsheet but you say that the data is all
seperated by commas? Can you not use 'Data'....'text to
columns'.....'delimited'....comma as a seperator?

"Rmcnaught" wrote:

>
> Again, I must turn to the Excel Power Users with another dilemna. In the
> attached XLS I have 4 columns of data. My problem is extracting the data
> in columns B, C & D. I need to extract each color type to it's own
> individual cell. Example: I need 131A-Dark Gray placed in cell A1,
> 133-Blue placed in cell A2, 451-Burgundy placed in A3, etc, etc. The
> Data from columns C & D would also be placed in it's own row in the
> same way. Also as you can see each cell doesn't contain the same number
> of entries. This is for a project that is almost complete except for
> this data. I have tried to obtain this data for 3 days now.
>
> Is there a way with a formula I can extract the individual color
> options for each item into a new sheet or am I going to have to do
> these entries manually, Yikes, I hope not I have 275 items. Someone
> suggested a Pivot Table and I have played with that for a whole day
> without any success. I feel like an idiot!!!!! I know that someone
> out there must know something
>
> I now turn this over to all the brains.
>
> Rich
>
> Here is some additional information that may help. Each one of the
> color options in the cell is seperated by a comma so is there a way to
> get the data to the left and between the commas?
>
>
> +-------------------------------------------------------------------+
> |Filename: Options.pdf |
> |Download: http://www.excelforum.com/attachment.php?postid=5049 |
> +-------------------------------------------------------------------+
>
> --
> Rmcnaught
> ------------------------------------------------------------------------
> Rmcnaught's Profile: http://www.excelforum.com/member.php...o&userid=36458
> View this thread: http://www.excelforum.com/showthread...hreadid=562528
>
>

Thanks Tim, I used that and it worked really good. Much easier than entering them all over.