BUY EXCEL BOOKS ONLINE: 1. VBA & Macros      2. VBA for Modelers      3. Excel 2013 VBA and Macros     
4. Excel VBA for Dummies      5. Excel with VBA & .NET      6. Mastering VBA      7. Excel 2013 Programming

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.
Click here to download the Split Text Tool or ....

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)
     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

Anonymous said...

Why not use vbLf instead of Chr(10)?

Ashish Jain said...

Yes, Point taken, we could use any :)

Anonymous said...

I really like the idea you show in your picture.
I'm not rally good at VBA, so maybe you could help me?
If I download you split text tool, and execute the macro, it doesn't give me the results you show in your picture, can you maybe explain this to me?

Thanks in advance!