Pulling all data left of a certain word surrounded by spaces

Hi all,

I'm trying to break down a list of meetings into separate columns with only the relevant information. I need to split Column A, which lists the company that sent a message and the one that received it, into two separate columns--one for the "from" company and one for the "to" company. Column A has a standard format of "COMPANYANAME to COMPANYBNAME." The problem is that I can't figure out how to make Excel split around the word "to." I've tried a combination of LEFT/LEN/SEARCH to get a rough list, but there are quite a few discrepancies. Any recommendations on how to make the text that comes before "to" appear in one separate column? I got the company that receives the message by using =RIGHT(C2,LEN(C2)-2) but the non-breaking spaces are giving me trouble for the company that sent the message.

Example...there are many cells in Column A with data formatted like the below
ABC Company to Mega Corporation, Inc.
or
WallyMart Co. to River Company, Ltd.

I want to rip the "ABC Company" and "WallyMart Co." into a separate cell.

Thanks!

Hi Glenn,

Thanks for the quick response! I've just tried that formula and it has a similar problem as the one I was using previously in that it will remove the data after "to" anywhere in the cell. Any company that has "to" in their name somewhere -- like Mitotech -- ends up with missing data. Is there a way to capture the spaces around the "to" so that Excel will only look for " to "? I inserted CHAR(160) in there to no avail. Thanks!