Help - convert text in single cell to multiple rows

[tay_olivia]

hi all, I'm looking for a solution to the following problem:

E.g. this is text data appearing in one single cell in excel:

"Morgan Stanley (Asia) Ltd
Cazenove Asia Ltd
Morgan Stanley (Asia) Ltd"

Would like to to be split into 3 excel rows, showing:
Morgan Stanley (Asia) Ltd
Cazenove Asia Ltd
Morgan Stanley (Asia) Ltd

Is this possible? WHat is the function that should be used to automat
this process

Attachment filename: samplexls.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=44462

 

[BrianB]

You will probably have to adapt this by changing the + or - numbers.

Say your data is in A1
In B1
=LEFT($A1,FIND("Ltd",$A1)+2)
in C1
=LEFT(MID($A1,LEN(B1)+2,255),FIND("Ltd",MID($A1,LEN(B1)+2,255))+2)
in D1
=MID(A1,LEN(B1)+LEN(C1)+3,LEN(A1)-LEN(B1)-LEN(C1)-3