Single Cell to Rows

[Blessy Clara]

Hi I have an excel sheet - the Data in the sheets are as - a single cell contains multiple lines of data - I wanna split the lines within the cell in to separate row -

[TABLE="width: 500"]
<tbody>[TR]
[TD]Phil Ross
Mc Lean David
Ross Andrews
Matthew Anders
Sandra

[/TD]
[TD]21359877
25789563
4879652
2147631
24178965[/TD]
[TD]email1
email2
email3
email4
email5

[/TD]
[/TR]
[TR]
[TD]Requirement[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Phil Ross[/TD]
[TD]21359877[/TD]
[TD]email1[/TD]
[/TR]
</tbody>[/TABLE]
Likewise each cell data split into separate columns,
Thank you - Please help

 

[Rick Rothstein]

Hi I have an excel sheet - the Data in the sheets are as - a single cell contains multiple lines of data - I wanna split the lines within the cell in to separate row -

[TABLE="width: 500"]
<tbody>[TR]
[TD]Phil Ross
Mc Lean David
Ross Andrews
Matthew Anders
Sandra

[/TD]
[TD]21359877
25789563
4879652
2147631
24178965[/TD]
[TD]email1
email2
email3
email4
email5

[/TD]
[/TR]
[TR]
[TD]Requirement[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Phil Ross[/TD]
[TD]21359877[/TD]
[TD]email1[/TD]
[/TR]
</tbody>[/TABLE]
Likewise each cell data split into separate columns,
Thank you - Please help

Is all of that data (5 rows and 3 columns) all in a single cell or are the columns already separate? If all in the same cell, what character or characters are separating what will end up in separate columns... spaces or tabs?

 

[Blessy Clara]

Hi Rick Rothstein
Thank you for the response - No the Cols are separate -
This is how the data looks -
https://www.dropbox.com/s/0k29tjqf75foes4/EB.xlsx?dl=0

 

[kweaver]

What separates the "lines" in each cell? CHAR(10) ?

If the cells are in A1, for example, I did this in B1, C1, and D1, respectively:

B1:

=LEFT(A1,FIND(CHAR(10),A1)-1)

C1:

=MID($A1,FIND(CHAR(10),$A1,FIND(CHAR(10),$A1))+1,(FIND(CHAR(10),$A1,FIND(CHAR(10),$A1)+1))-(FIND(CHAR(10),$A1,FIND(CHAR(10),$A1))+1))

D1:

=MID($A1,FIND(CHAR(10),$A1,FIND(CHAR(10),$A1)+1)+1,LEN($A1)-FIND(CHAR(10),$A1,FIND(CHAR(10),$A1)+1)+1)

 

[kweaver]

I didn't see your drop box until after I posted. Will see about this with your data.

 

[Rick Rothstein]

Hi Rick Rothstein
Thank you for the response - No the Cols are separate -
This is how the data looks -
https://www.dropbox.com/s/0k29tjqf75foes4/EB.xlsx?dl=0

Assuming the final output will always be less than about 65000 rows of data, run this macro with your data sheet active and it will output the desired result to Sheet2 (change as needed)...

[table="width: 500"]
[tr]
	[td]Sub BlessyClara()
  Dim LastRow As Long, Col As Long, ColData As Variant
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  For Col = 1 To 3
    ColData = Split(Join(Application.Transpose(Range(Cells(1, Col), Cells(LastRow, Col))), vbLf), vbLf)
    Sheets("[B][COLOR="#0000FF"]Sheet2[/COLOR][/B]").Cells(1, Col).Resize(UBound(ColData) + 1) = Application.Transpose(ColData)
  Next
  Sheets("[B][COLOR="#0000FF"]Sheet2[/COLOR][/B]").Columns("A:C").AutoFit
End Sub[/td]
[/tr]
[/table]

 

[kweaver]

Great macro, Rick.

Now, if Clara wants the names, then, I suppose that's a phone #, then the email in D, E and F columns:

D2 filled down:

=SUBSTITUTE(LEFT(A2,FIND(" ",A2)-1)&" "&RIGHT(A2,LEN(A2)-FIND("=",A2)),"""","")

E2 filled down:

=LEFT(C2,FIND("""",C2)-1)

And F2 filled down is simply =B2

 

[Blessy Clara]

Hi Rick
:O Wow - That is amazing - Thank you Rick

Hi kweaver
Yeah Thank you very much for the Formula - Yeah it helps me further clean up the Data - That was indeed supposed to be my next step