I have a HUGE spreadsheet of cross reference information.

One column sometimes contains several model numbers - not just one.

I'd like to be able to rip out the data between commas in that column and
make a new row for just that one piece of data and then put all of the same
data from the columns back around the data... Hard to describe. Here is an
example.

red....tree, bush, shrub, grass....money....car
blue..bike.................................cat.... .....truck
tan...apple, pear.......................dog........plane

Id like it to look like this:

red...tree.................................money.. ...car
red...bush................................money... ..car
red...shrub...............................money... ..car
red...grass...............................money... ..car
blue..bike.................................cat.... ......truck
tan...apple................................dog.... .....plane
tan...pear.................................dog.... .....plane

That is what we are trying to do!

Thanks for your help

hi,
not really. you can use the mid function.
if you had the word RedApple in cell A1, the formula in b1 would look like
this...
=MID(A1,1,3). the results would be "red" in B1. you can get rid of the
formula by coping the formula and paste special Values. this formula is
considered a text formula but it works on numbers too.
you did mention a HUGE worksheet so you may have a little work so i'm not
sure if i helped or not.

Good Luck
Regards
FSt1

"David B" wrote:

I have a HUGE spreadsheet of cross reference information.

One column sometimes contains several model numbers - not just one.

I'd like to be able to rip out the data between commas in that column and
make a new row for just that one piece of data and then put all of the same
data from the columns back around the data... Hard to describe. Here is an
example.

red....tree, bush, shrub, grass....money....car
blue..bike.................................cat.... .....truck
tan...apple, pear.......................dog........plane

Id like it to look like this:

red...tree.................................money.. ...car
red...bush................................money... ..car
red...shrub...............................money... ..car
red...grass...............................money... ..car
blue..bike.................................cat.... ......truck
tan...apple................................dog.... .....plane
tan...pear.................................dog.... .....plane

That is what we are trying to do!

Thanks for your help