• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel Macros to Split Text on two New Lines

Marc L

Excel Ninja
For starters an Excel basic features VBA demonstration to paste to the Sheet2 (Input) worksheet module :​
Sub Demo1()
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
    With [B1].CurrentRegion.Columns(1).Offset(1)
        .Replace vbLf & vbLf & vbLf, vbTab, 2
        .Replace vbLf & vbLf, vbTab, 2
        .Replace vbLf, vbBack, 2
        .TextToColumns , 1, xlTextQualifierNone, True, True
        .CurrentRegion.Replace vbBack, vbLf, 2
        .CurrentRegion.ColumnWidth = 15
    End With
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
End Sub
Do you like it ? So thanks to click on bottom right Like !​

Marc L

Excel Ninja
The page gives a way but his expected result is not the same as you can check with the attachment Ouput worksheet ...​


Well-Known Member
If you have O365/Excel2019 in any cell on row 2:
copy down.
All cells need to have wordwrap on.

Separately, you could have a Power Query solution if you're interested?