Excel Question: Line Split / delimiter on an empty row

[ESACAWIP]

0
Example : I have a single cell (yes, single cell) that contains all this information.
+---------+
| ghijkl |
| aededd |
| |
| ededed |
| ededfe |
| |
| efefeef |
| efefeff |
+---------+

Is there anyway I can split this into 3 cells ?
+---------+ ---------+ --------- +
| ghijkl | ededed | efefeef |
| aededd | ededfe | efefeff |
| | | |
+---------+----------+----------+

I've tried using the delimiter tool and various vb code but all seem to split at each single line break but none which can split on an empty line break/row.
I apologize if I am using these terms incorrect.

Any help is appreciated, thank you!

 

[ESACAWIP]

I apologies, the text formatted very strangely. The first box is how the data is presented and the second box is how I need the data parsed.

 

[sandy666]

use XL2BB to post your example
read there how to use it
remember to post a representative example

 

[ESACAWIP]

Thank you Sandy666,

I actually found a working code but unsure of how to turn this into a loop that goes down the column. Do you think you can help? Much appreciated!




Sub CellSplit()
Dim MyInput As String
Dim TempText As String, result As String
Dim sSplit

MyInput = Range("A1")
TempText = Replace(MyInput, Chr(10), "@")

sSplit = Split(TempText, "@@")

For i = LBound(sSplit) To UBound(sSplit)
Cells(1, i + 2) = Replace(sSplit(i), "@", Chr(10))
Next i

End Sub

 

[sandy666]

sorry, I can't. I don't like vba

btw. I you want to post a code wrap the code in code tags

 

[navic]

This is a modified VBA code that is publised by @p45call

'Option Explicit
Sub blah()
Set Destn = Range("D1")
For Each are In Columns("A:A").SpecialCells(xlCellTypeConstants, xlTextValues).Areas
  are.Copy Destn
  Set Destn = Destn.Offset(, 1)
Next are
End Sub

 

[Peter_SSs]

Try this with a copy of your data.
I have assumed data in column A

Sub SplitEm()
  With Range("A1", Range("A" & Rows.Count).End(xlUp))
    .Replace What:=vbLf & vbLf, Replacement:=";", LookAt:=xlPart
    .Replace What:=vbLf, Replacement:="#", LookAt:=xlPart
    .TextToColumns DataType:=xlDelimited, Tab:=False, Semicolon:=True, Comma:=False, Space:=False, Other:=False
    .CurrentRegion.Replace What:="#", Replacement:=vbLf, LookAt:=xlPart
  End With
End Sub