Parsing a cell that has mulitiple carraige returns (ALT-ENTER) into multiple rows

[cmschu]

I'm trying to figure out how I can automate parsing out cells that have multiple line items entered into it -- out into a row per item. Example content. The following is all in one cell and I have many of these. How do I find the hard return in order to break them out into different rows.

ActiveCell = B2
* Interoperability: Address operating in a high matrix environment <ALT-ENTER>
* Finance: Underlying requirements for SOW <ALT-ENTER>
* Change Mgmt.: Navigate constant changes to work more effectively

I want to insert blank rows below this cell and break out each line out to a single row / cell for each underneath it

B3 = * Interoperability: Address operating in a high matrix environment
B4 = * Finance: Underlying requirements for SOW
B5 = * Change Mgmt.: Navigate constant changes to work more effectively

 

[DanteAmor]

Try this macro

Sub Parsing_cell()
  Dim i As Long, j As Long
  Dim c As Variant
  
  Application.ScreenUpdating = False
  For i = Range("B" & Rows.Count).End(3).Row To 2 Step -1
    c = Split(Range("B" & i), Chr(10))
    For j = UBound(c) To 0 Step -1
      If j = 0 Then
        Range("B" & i).Value = c(j)
      Else
        Range("B" & i + 1).EntireRow.Insert
        Range("B" & i + 1).Value = c(j)
      End If
    Next j
  Next i
End Sub

 

[cmschu]

works perfect thanks!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[Peter_SSs]

I'm guessing that you have other columns that need to be kept aligned but if it was just a single column then you could also do this.

Sub SplitToRows()
  Dim a As Variant
  
  a = Split(Join(Application.Transpose(Range("B2", Range("B" & Rows.Count).End(xlUp))), Chr(10)), Chr(10))
  Range("B2").Resize(UBound(a) + 1).Value = Application.Transpose(a)
End Sub