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