seperate multiple lines

[dave w]

I have an excel file that contains multiple lines in 1 cell - How do I seperate these 2 lines into seperate cells?

 

[MickG]

Try this:-
Multiline data in cells in column "A" results Column "B"

[COLOR=navy]Sub[/COLOR] MG27Aug31
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Spt [COLOR=navy]As[/COLOR] Variant
[COLOR=navy]Dim[/COLOR] n [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] c [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
ReDim Ray(1 To Rng.Count * 10)
    [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
        Spt = Split(Dn, Chr(10))
        [COLOR=navy]For[/COLOR] n = 0 To UBound(Spt)
            c = c + 1
            Ray(c) = Spt(n)
        [COLOR=navy]Next[/COLOR] n
    [COLOR=navy]Next[/COLOR] Dn
Range("B1").Resize(c) = Application.Transpose(Ray)
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[jim may]

Excel Workbook
	A	B	C
1	This is line one and this is line two	This is line one	and this is line two
Sheet1

Excel 2007

Cell Formulas
Range	Formula
B1	=LEFT(A1,FIND(CHAR(10),A1)-1)
C1	=MID(A1,LEN(B1)+2,99)