Split the Text with Newline Characters (Alt + Enter)

Splitting the text is a common problem though the kind of split and degree of split may vary problem to problem. There are several ways to solve the split problem depending upon the problem like using formulas, macros or manually. Similarly, would you like to split horizontally or vertically. See a similar situation below with problem (left) and the solution (right). If, you came across such problem usually or find it interesting then go ahead.

What can we do now?

1. Use Formulas :  Bit difficult and may not be robust.
2. Data -> Text to Columns :  It won't work with new line character, moreover, it split horizontally only.
3. 3. VBA Macros :  Yes, absolutely correct.

---

Sub Split_Text(srcRange as Range, destRange as Range)
' As per the image above, provide srcRange as Column A data range
' and destRange as starting cell of Column D for this example. 
' Feel free to change as per your requirements.
     Dim splitVals As Variant
     Dim i as Byte
     On Error Resume Next
     'Reading Input
     For Each mycell In srcRange.Cells
     'Chr(10) or vbLf is the newline character.
          splitVals = Split(mycell.Offset(0,1).Value, vbLf)
          For i = 0 To UBound(splitVals)
               'Writing Output to the Destination 
               destRange.Value = myCell.Value
               destRange.Offset(0,1).Value = splitVals(i)
               Set destRange = destRange.Offset(1, 0)
          Next
     Next
     On Error GoTo 0
End Sub

'Usage Example for the above image.
Sub Do_Splits()
     Call Split_Text(Range("A2:A4"),Range("D2"))
End Sub

---

How to use? To run these macros, simply press F5 while placing the cursor inside the macro code.
OR From MS Excel window, press 'Alt+F8' to select the macro and then hit the 'Run' button.

---

References For more information about the functions used inside above macros, type the following text on a module sheet:

• Split
• UBound
• Chr

Highlight the function about which you want more information, and then press F1.

---

Supported versions of MS Excel: APPLIES TO

• Microsoft Office Excel 2010
• Microsoft Office Excel 2007
• Microsoft Office Excel 2007
• Microsoft Office Excel 2003
• Microsoft Excel 2002 Standard Edition
• Microsoft Excel 2000 Standard Edition

---