Transform multiple line cell into multiple cell

[LucaTM]

Is there a simple way to transform a cell with multiple lines into multiple cell?

From this:

Test Test 2 Test 3	Bla	BlaBla
asdf	asdff	asder

<tbody>
</tbody>

To this:

Test	Bla	BlaBla
Test 2
Test 3
asdf	asdff	asder

<tbody>
</tbody>

Because I have lots of this cases it can't do it manually.

 

[MickG]

Try this :- Results sheet2 starting "A1".

[COLOR="Navy"]Sub[/COLOR] MG10Feb38
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] oVal [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Ray()


[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    oVal = Split(Dn, Chr(10))
        [COLOR="Navy"]If[/COLOR] UBound(oVal) > 0 [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]For[/COLOR] n = 0 To UBound(oVal)
                c = c + 1
                    ReDim Preserve Ray(1 To 3, 1 To c)
                    [COLOR="Navy"]If[/COLOR] n = 0 [COLOR="Navy"]Then[/COLOR]
                        Ray(1, c) = oVal(n): Ray(2, c) = Dn.Offset(, 1): Ray(3, c) = Dn.Offset(, 2)
                    [COLOR="Navy"]Else[/COLOR]
                        Ray(1, c) = oVal(n)
                    [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]Next[/COLOR] n
        [COLOR="Navy"]Else[/COLOR]
                c = c + 1
                    ReDim Preserve Ray(1 To 3, 1 To c)
                    Ray(1, c) = Dn: Ray(2, c) = Dn.Offset(, 1): Ray(3, c) = Dn.Offset(, 2)
        [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn


Sheets("Sheet2").Range("A1").Resize(c, 3) = Application.Transpose(Ray)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[hiker95]

LucaTM,

Welcome to the MrExcel forum.

What version of Excel and Windows are you using?


What results are you looking for, 1. , or 2.?

Excel 2007
	A	B	C
1	Test Test 2 Test 3	Bla	BlaBla
2	asdf	asdff	asder
3
4	1. To this?
5	Test	Bla	BlaBla
6	Test 2
7	Test 3
8	asdf	asdff	asder
9
10	2. Or, to this?
11	Test	Bla	BlaBla
12	Test 2	Bla	BlaBla
13	Test 3	Bla	BlaBla
14	asdf	asdff	asder
15
Sheet1