In Cell A4, all data is together in one cell. It should be parsed and look like A8 thru A14. Please see attachment and advise how to do this. Thanks.

Hi,

Am Fri, 20 Dec 2013 21:44:26 +0000 schrieb horacehill:

|Filename: Parsing 2.jpg |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=935|

try:

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


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

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