Looking to separate and duplicate rows based on values in one column.

Looking to separate and duplicate rows based on values in one column.

I have a poorly formatted spreadsheet whereby first names for different family members were all included in one cell. The rest of the data in the row is fine... only the first name cell was combined.

Something like...

John,Jane,Charlie,Shirly

and others like...

Frank and Mindy

I need to duplicate the entire row of data for all cells in this column that have multiple names in this first name field. The rest of the cells in the row such as last name, phone, address, etc... will need to to stay the same and also be duplicated to any new rows created. Please see attached image as it hopefully will help.

What I need is these names to each appear on their own row but maintain the data of the rest of the cells in that row.

Hmmm. I'm not sure why you can't see the image. It does show up for me.

Here is a direct link to the example.

http://www.pixogee.com/images/example_help.png

In answer to your question, sometimes there is a space and the word 'and' and sometimes just commas. The example image should hopefully explain it better.

Appreciate the quick responses here! @pjwhitfield - Super close to what I need. I ran it and the cells that contain first names like John and Jane are separated but Jane ends up with a single space in front of her first name on her row.

Thanks pjwhitfield. All I needed to do was add a space after the and...

Please Login or Register  to view this content.

That worked! Again, thanks so much!

Ah! Very good! Thanks pjwhitfield! This was very helpful and obviously saved me gobs of time!