Parsing

[horacehill]

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

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

 

[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.

 

[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(10)&$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.