Trying to use a text string to separate values (If/Find question)

[Geoff1184]

I post here from time to time and have found very helpful replies to my questions. Thanks a lot for that. Today i ran into something that i can't seem to solve and thought i'd shoot it out to the forum. I've got a lot of cells that contain a list of companies within each cell. Companies are separated with semi-colons and cells vary in the amount of companies that are listed. The last company in the list does not have a semi-colon and if the cell contains one company, there is no semi-colon.

What i want to do is list each company separately in a new column. Is there a way to do this?

Example cell:

"Citigroup, Inc. (NYSE:C) (Investment Banking (Advisory Firm));

Credit Suisse Group AG (SWX:CSGN) (Investment Banking (Advisory Firm));

JPMorgan Chase & Co. (NYSE:JPM) (Investment Banking (Advisory Firm));

Morgan Stanley (NYSE:MS) (Investment Banking (Advisory Firm))"

Formula result would allow you to drag across four columns and get the individual companies only: IE

Column B = Citigroup, Inc. (NYSE:C) (Investment Banking (Advisory Firm))
Column C = Credit Suisse Group AG (SWX:CSGN) (Investment Banking (Advisory Firm))
Column C = JPMorgan Chase & Co. (NYSE:JPM) (Investment Banking (Advisory Firm))
Column C = Morgan Stanley (NYSE:MS) (Investment Banking (Advisory Firm))

Thoughts? Any help would be appreciated. This is where i am but it's obviously wrong.

=IF(FIND(";",A1)>0,LEFT(A1,FIND(";",A1)),A1) - It needs an iserr to balance cells with no ";" and a way to be repeated across columns.

[L-Drr]

You don't have to use a formula for this. Go to Data -> Text to columns and use ; as denominator

[Geoff1184]

The cells have break between values which throws off the Text to columns. How do you remove breaks, i tried a find and replace on <br> with no success.

[Geoff1184]

Nevermind, found Clean.