Split the contents from single cell into multiple cell in excel 2010

[san.rathinapuri]

Hi Rick,

please refer the attached document with input and output.

The cell alignment and everthing i have mentioned exactly. please help me to sort out this.

I need to reduce my time using macros.


Thanks,
Santhosh

[alansidman]

For starters, you have merged cells. VBA does not like and will not usually work with merged cells. You will need to redesign your workbook so that there are no merged cells. Additionally, in your post you indicated that there are only 3 lines of data in each cell. This is obviously not the case. You have multiple rows of data (more than the three) indicated in your problem. It is difficult to provide you with an accurate solution if you don't provide accurate information on your issue.

[san.rathinapuri]

ok thanks for the reply Alansidman.

Before run this macro i will try to unmerge all the cells.

yes, there will be multiple sentences also in single cell, its not limited. It may vary from one cell to another cell.

Any solution for my document after unmerging..?

[Rick Rothstein]

Before run this macro i will try to unmerge all the cells.

yes, there will be multiple sentences also in single cell, its not limited. It may vary from one cell to another cell.

Any solution for my document after unmerging..?

You do not have to unmerge the cells manually, we can have the code do that. What we need to know is how uniform you layout is. So, you need to answer these questions about the content of Column C's filled cells (from Row 2 on downward)...

1) Does each cell have a Pre-requisite and a Steps section as shown in your example?

2) If so, can there be a varied number of items under each of them (your example shows only one in Pre-requisite and three in Steps... those numbers are not fixed at one and three, correct)?

3) Is the Pre-requisite section always set off from the Steps section by two Line Feeds (as shown in your example)?

4) This is an extremely important question... did you always merge enough cells to accommodate the number of individual lines of text that you want to put into their own cells? For your example, you showed four merged cells for the four numbered items in each cell, but if you had, say, two items in Pre-requisite and five items in Steps, would you have them in a merged set of four cells (like your example shows) or in seven cells because there are seven items?

5) Just double-checking... the split apart text is supposed to replace the original data in Column C, correct? I ask because you have a column headed by the title "Result" and I just want to make sure you don't want the output to go there.

[san.rathinapuri]

Hi Rick,

Thanks for the response. please find the answers below for your questions. i believe this will give you the clear picture.

1) Does each cell have a Pre-requisite and a Steps section as shown in your example?

NO, ONLY FEW COLUMNS WILL HAVE THE PRE-REQUISITE.
bUT THE STEPS WILL APPEAR IN EVERY COLUMN.

2) If so, can there be a varied number of items under each of them (your example shows only one in Pre-requisite and three in Steps... those numbers are not fixed at one and three, correct)?

YES,ITS NOT FIXED. IT MAY VARY CELL TO CELL.

3) Is the Pre-requisite section always set off from the Steps section by two Line Feeds (as shown in your example)?

YES. AS GIVEN IN THE PREVIOUS ATTACHMENT.

4) This is an extremely important question... did you always merge enough cells to accommodate the number of individual lines of text that you want to put into their own cells? For your example, you showed four merged cells for the four numbered items in each cell, but if you had, say, two items in Pre-requisite and five items in Steps, would you have them in a merged set of four cells (like your example shows) or in seven cells because there are seven items?

ALWAYS THERE WILL BE 4 CELLS MERGED.ITS NOT DEPENDS UPON THE NUMBER OF STEPS AND PRE-REQUISITE.

5) Just double-checking... the split apart text is supposed to replace the original data in Column C, correct? I ask because you have a column headed by the title "Result" and I just want to make sure you don't want the output to go there.

NO OUTPUT SHOULD GO THERE TO RESULT COLUMN.
JUST WE WANT TO SPLIT THE CONTENT FROM COLUMN C (FOR EXAMPLE) TO BELOW CELLS.


Thanks,
Santhosh

[Rick Rothstein]

ALWAYS THERE WILL BE 4 CELLS MERGED.ITS NOT DEPENDS UPON THE NUMBER OF STEPS AND PRE-REQUISITE.

NO OUTPUT SHOULD GO THERE TO RESULT COLUMN.
JUST WE WANT TO SPLIT THE CONTENT FROM COLUMN C (FOR EXAMPLE) TO BELOW CELLS.

Your posted workbook showed the result in the data column and it split the result into separate cells there (that is, it unmerged the merged cell). Are you now saying you want to keep the data cell as is and put the split out result in the Result column? Also, in order to put them into separate cells when the data is split and there are more than 4 split out rows, additional rows would need to be added to accommodate them... you do understand this, correct?