Delimite the text in cell

[G2K]

Hi,

i have to delimite the following data, written in a single cell.
Barclays PLC
Citi
Deutsche Bank AG
Goldman Sachs & Co
HSBC Holdings PLC
Merrill Lynch
Morgan Stanley & Co
RBS
UBS

excel showing small rectangle between two lines. how to delimite such text....

 

[G2K]

one of the column contains date. when we copy and paste it in new inserted cells it's format changes to dd/mm/yyyy from mm/dd/yy. and i am inable to change it in it's original format.

thnaks

 

[Peter_SSs]

Pleqase confirm...

Which is the correct format?

Edit: And which column is it?

 

[G2K]

mm/dd/yy is correct format for this report.

would request you to explain why i am not able to format these cells.

 

[Peter_SSs]

This may not solve all the date problems (dates are often tricky) but give it a try:

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Rearrange()<br>    <SPAN style="color:#00007F">Dim</SPAN> aSplit<br>    <SPAN style="color:#00007F">Dim</SPAN> lr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, r <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, newrows <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    lr = Range("A" & Rows.Count).End(xlUp).Row<br>    <SPAN style="color:#00007F">For</SPAN> r = lr <SPAN style="color:#00007F">To</SPAN> 2 <SPAN style="color:#00007F">Step</SPAN> -1<br>        aSplit = Split(Cells(r, "G").Value, Chr(10))<br>        newrows = <SPAN style="color:#00007F">UBound</SPAN>(aSplit)<br>        Rows(r + 1).Resize(newrows).Insert<br>        Cells(r, "G").Resize(newrows + 1) = Application.Transpose(aSplit)<br>        Cells(r, 1).Resize(, 6).Copy Destination:=Cells(r + 1, 1).Resize(newrows)<br>    <SPAN style="color:#00007F">Next</SPAN> r<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>