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

[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