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

[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