Help: Separate cell into multiple rows and copy contents of cells

Originally Posted by rtype16

If anyone could help it would be greatly appreciated. So what I have is a data sheet that I need to take one cell for instance it is 1991-1993 and seperate it into different rows so row 1 would be 1991, row 2 1992 and row 3 1993. Than I also need it to copy all the existing data in that row to each.

Example of data:
row 1: (Column1)1991-1993 (Column 2)a (Column 3)b (Column 4)c

What I need it to up up as is:
row 1: (Column 1)1991 (Column 2)a (Column 3)b (Column 4)c
row 2: (Column 1)1992 (Column 2)a (Column 3)b (Column 4)c
row 3: (Column 3)1993 (Column 2)a (Column 3)b (Column 4)c

Please help.

You could do it reasonably easily with some VBA code, which would determine the first and end year, and work out how many years were in between, and then in a loop write back the 3 (or more depending on the span of years) rows required.

Alternatively a non macro solution would be to copy your data rows (assuming there are more than the one in this example) paste them back as a further two blocks of data. Then use the Edit Find Replace, and in the first block replace the " - 1993" with null, in the second block replace the whole of "1991 - 1993" with "1992" and do something similar with the third block.

HTH