• 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

    Hui...

  • 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 :​
Code:
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 ...​
 

p45cal

Well-Known Member
If you have O365/Excel2019 in any cell on row 2:
=TEXTSPLIT(SUBSTITUTE(B2,REPT(CHAR(10),3),REPT(CHAR(10),2)),REPT(CHAR(10),2),,TRUE)
copy down.
All cells need to have wordwrap on.

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