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.

 

[sn152]

Hi,

Please refer this link - http://chandoo.org/wp/2011/08/23/split-text-on-new-line-macro/

 

[Somendra Misra]

Hi,

Try this:

Option Explicit

Sub test()

Dim lr As Long
Dim i As Long
Dim oc As Long
Dim str() As String
Dim j As Long

lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 To lr
    oc = 3  ' Output column Number
    str = Split(Range("A" & i), Chr(10))
    For j = LBound(str) To UBound(str)
        Cells(i, oc) = str(j)
        oc = oc + 1
    Next j
    oc = 3
  
Next i
End Sub

I had assume data in column A starting from A1,

Regards,

 

[Marc L]

Hi,

just needs one codeline using TextToColumns method !​