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

[san.rathinapuri]

I have to Split the contents from single cell into multiple cell in excel 2010.

Input:

Cell1 contains 3 sentences,

Step1: Am santhosh from Coimbatore.
Step2: Am working as testing Engineer.
Step3: I need solution for this.


I need to cplit the above cell1 into 3 cells below (Cell2, cell3, cell4)


Cell1:
Step1: Am santhosh from Coimbatore.

Cell2:
Step2: Am working as testing Engineer.

Cell3:
Step3: I need solution for this.

Please someone help me. I need to do the same for lot of cells in the document. Any short cut or macros to do this. pls help me on this.
email: san.rathinapuri@gmail.com

[Rick Rothstein]

Select the cell or cells that you want to split apart and then run this macro...

Sub SplitSelectedCellsDown()
Dim Cell As Range, Sentences() As String
For Each Cell In Selection
Sentences = Split(Cell.Value & vbLf, vbLf)
Cell.Offset(1).Resize(UBound(Sentences)) = Application.Transpose(Sentences)
Next
End Sub

[san.rathinapuri]

Hi Rick,

Thanks a lot for your solution.

It reduces the 50% of my work, still i have to modify few things as like below,

Input: Cell1 contains 3 sentences,

Step1: Am santhosh from Coimbatore.
Step2: Am working as testing Engineer.
Step3: I need solution for this.


I need to split the above cell1 into 3 cells below (Cell2, cell3, cell4). Your code works here but..
Before executing the macro given by you, i need to create 3 empty cells below that to get the proper output. Otherwise its overwriting the existing content.

Any solution to create the 3 empty cells first (Based on the number of sentences available in cell1) and then seperate the contents as like your macro.

[Rick Rothstein]

This revised macro should work for you...

Sub SplitSelectedCellsDown()
Dim Cell As Range, Sentences() As String
For Each Cell In Selection
Sentences = Split(Cell.Value & vbLf, vbLf)
Cell.Offset(1).Resize(UBound(Sentences)).Insert xlShiftDown
Cell.Offset(1).Resize(UBound(Sentences)) = Application.Transpose(Sentences)
Next
End Sub

[san.rathinapuri]

Hi Rick,

Thanks a lot for the code. Its working good for single cell selection.

Am facing small issue hile running this macro.

Input:

Cell A1 contains 3 sentences,
Step1: Am santhosh from Coimbatore.
Step2: Am working as testing Engineer.
Step3: I need solution for this.

Cell A2 contains 3 sentences,
Step1: Am santhosh from Chennai.
Step2: Am working as development Engineer.
Step3: I need solution for this also.

If i select this both cell at a time and run the macro, its going on running without any end point. i need to force close this macro to stop. Can you please please provide solution for this.

[san.rathinapuri]

Hi Rick,

Can you please share your email id. I will send the excel sheet, it will be better to understand my requirements clearly.

Thanks,
Santhosh

[alansidman]

In the reply window, click on the Go Advanced button. Look for the manage attachments button and follow the wizard to attach your workbook onto this forum.

[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?

[san.rathinapuri]

Hi Rick,

Don worry about the result column, we are not going to use that now.

Currently for example, 5 sentences are placed in column C. We need to unmerge and split the cells in column c and need to paste it in seperate lines below.

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 --> YES CORRECT.

The format will see same like my previous attachment.