Problem with Split Cells

[bjohnson112069]

Hi. I am having a problem splitting cell into columns. I am trying to split the cell into columns using the Line Feed (LF) as the delimiter. I am using Data | Text to Columns | Other and pressing Ctrl-J to specify the delimiter. This seems simple enough but the only way I can get it to work is if I double-click on each cell and hit Enter prior to Text to Columns. This is manageable for 2 rows but a not tenable for 15,000 rows.

Using the attached image as a reference... Cell A2 data was entered by using Alt-Enter ... LINE1(Alt-Enter)LINE2(Alt-Enter)LINE3(Alt-Enter)LINE4, press Enter. Cell A3 data was created by selecting A2, copy and paste into Notepad. Select the string in Notepad, copy and paste into Cell A3. A2 converts as expected; A3 does not. If I double-click on A3, press Enter and re-execute Text to Columns then both rows split as expected.

Ultimately, I have a file with 15,000 rows in it and each cell has a multi-line string contained in it. The strings are exported from another system and will need to be formatted for the Text to Columns approach to work. Double-click will work but looking for something far more efficient. Any insight is appreciated. Thank you in advance.

 

[jolivanes]

This deletes Column A when finished. You might want to delete that.

Sub Does_This_Do_It()
Dim x() As String, i As Long
    For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row    '<----- Starts at A2 as it is assumed that you have a header. If not, change the 2 to a 1
        With Cells(i, 1)
            x = Split(.Value, Chr(10))
            .Offset(, 1).Resize(, UBound(x) + 1).Value = x
        End With
    Next i
Columns(1).Delete
End Sub

 

[jolivanes]

If you want to paste starting in Column A instead of Column B as in the code from Post #2, change this line

.Offset(, 1).Resize(, UBound(x) + 1).Value = x

to this

.Resize(, UBound(x) + 1).Value = x

 

[kvsrinivasamurthy]

Have you tried. Select the whole column Range.
Data | Text to Columns | Other and pressing Ctrl-J
Because this worked for me.

 

[kvsrinivasamurthy]

This macro code works. Range is A23:A26. Change accordingly

Sub SplitWithLF()
Dim M, cel,R
Application.ScreenUpdating = False
Set R= range("A23:A26")
For each cel in R
M = Split(cel, Chr(10))
cel.Resize(1, UBound(M) + 1) = M
NextCelT
Application.ScreenUpdating = True
End Sub