title: Split text in cells at line breaks
body: <p>I am working on an Excel spreadsheet that has data in 39 columns. One of these columns, column AJ, is a description field, and contains text describing the row item in detail. This text inside the cell sometimes is more than one line long and new lines have been started by pressing (ALT+Enter).</p>
  
  <p>I need to be able to copy the entire sheet and place it all in another sheet (existing sheet), but with a new row for each new <em>line</em> in column AJ, as follows:</p>
  
  <pre><code>Column A     Column B     Column AJ
  Electrical   Lighting     This is line one of the text
                            And in the same cell on a new line
  </code></pre>
  
  <p>This is the required result:</p>
  
  <pre><code>Column A     Column B     Column AJ
  Electrical   Lighting     This is line one of the text
  Electrical   Lighting     And in the same cell on a new line
  </code></pre>
  
  <p>I have searched the forums for similar code, but I am having trouble adapting it for my own purpose.</p>
  
  <p>UPDATE: Not sure exactly why this has been closed, assume you maybe want an example of some code. I was using the below macro, that I found on the internet:</p>
  
  <pre><code>Sub Splt()
  Dim LR As Long, i As Long
  Dim X As Variant
  Application.ScreenUpdating = False
  LR = Range("AJ" &amp; Rows.Count).End(xlUp).Row
  Columns("AJ").Insert
  For i = LR To 1 Step -1
      With Range("B" &amp; i)
          If InStr(.Value, ",") = 0 Then
              .Offset(, -1).Value = .Value
          Else
              X = Split(.Value, ",")
              .Offset(1).Resize(UBound(X)).EntireRow.Insert
              .Offset(, -1).Resize(UBound(X) - LBound(X) + 1).Value = Application.Transpose(X)
          End If
      End With
  Next i
  Columns("AK").Delete
  LR = Range("AJ" &amp; Rows.Count).End(xlUp).Row
  With Range("AJ1:AK" &amp; LR)
      On Error Resume Next
      .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
      On Error GoTo 0
      .Value = .Value
  End With
  Application.ScreenUpdating = True
  End Sub
  </code></pre>
  
  <p>But it is not working, maybe I have adapted it incorrectly.</p>