Hi,

Am Sat, 21 Dec 2013 09:34:18 +0100 schrieb Claus Busch:

Sub Parsing()
Dim myStr As String
Dim myArr As Variant

myStr = Range("A4")
myArr = Split(myStr, Chr(10))
Range("A8").Resize(rowsize:=UBound(myArr) + 1) _
= WorksheetFunction.Transpose(myArr)
End Sub

if you want a formula try:
=MID(LEFT(CHAR(10)&$A$4,FIND("#",SUBSTITUTE(CHAR(1 0)&$A$4,CHAR(10),"#",ROW(A2))&"#")-1),FIND("#",SUBSTITUTE(CHAR(10)&$A$4,CHAR(10),"#", ROW(A1))&"#")+1,99)
and copy down


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2