split alt+enter values of a cells into multiple cells

[bernandas]

dear all,

pl help me with macro for the following scenario. want to put the alt+enter values of same cells into multiple cells. have given base file and base file with required output. kindly do the needful.
Untitled.png

[Kenneth Hobs]

Welcome to the forum!

Add this to a Module, select you cells to parse, and run the macro.

Sub SplitSelectionByVBLFToRight()  Dim c As Range, s() As String
  On Error Resume Next
  For Each c In Selection
     s() = Split(c, vbLf)
    c.Offset(, 1).Resize(, UBound(s) + 1).Value = s()
  Next c
End Sub

[p45cal]

You can also use Text-To-Columns, either programmatically or manually:
Programmatically, select your cells first then run:

Sub blah()
    Selection.TextToColumns Destination:=Selection.Offset(, 1), OtherChar:=vbLf
End Sub

Where the 1 in .Offset(,1) means 1 cell to the right of the source cells, change that to whatever number you want, or if you're content to have the result overwrite the source cells as is the default in text-to-column operations, then this shorter verion will do:

Sub blah()
    Selection.TextToColumns OtherChar:=vbLf
End Sub

Manually: Select your source cells, bring up the text-to-columns dialogue box (Data tab, Data Tools section) and in step 1 choose Delimited, in step 2 choose only Other: and put the text cursor in the field and on the keyboard, hold down the Alt key and tap on the number keypad 0010, then let go of the Alt key, then in step 3 choose your destination if you need to and press Finish.

[p45cal]

Oh groan, you cross posted this here: http://www.mrexcel.com/forum/excel-q...ple-cells.html
Please read http://www.excelguru.ca/content.php?184 and with netiquette in mind, please 'do the needful' there and anywhere else you may have cross-posted to.