Excel Macros to Split Text on two New Lines

[madcom]

In my search I came across the link https://chandoo.org/wp/split-text-on-new-line-macro/

In excel sheet can I get the split text on two New Lines
I have attached the example sheet.

 

[Marc L]

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 !​

​

 

[6StringJazzer]

I don't understand the question. The page you linked gives you the exact method to do this.

 

[Marc L]

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

​

 

[p45cal]

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?