[horacehill]

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.

[Claus Busch]

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

[Claus Busch]

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