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 -

Phil Ross Mc Lean David Ross Andrews Matthew Anders Sandra	21359877 25789563 4879652 2147631 24178965	email1 email2 email3 email4 email5
Requirement
Phil Ross	21359877	email1

<tbody>
</tbody>

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 -

Phil Ross Mc Lean David Ross Andrews Matthew Anders Sandra	21359877 25789563 4879652 2147631 24178965	email1 email2 email3 email4 email5
Requirement
Phil Ross	21359877	email1

<tbody>
</tbody>

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