Column Text Split

[tbrynard01]

I have a column of data that I would like to split in first and last names, however there are lines that have a company name and some first names are Joe and Jane for example when I use Text to Columns with spaces I get several columns. Was wondering if there was a better way to do that. Here are some examples:
John and Jane Doe
Microsoft Corporation
A B Corporation
Peter Plum and Family

Any advice will be appreciated.

Thank you

 

[kweaver]

What version of Excel are you running (please modify your account details)?
How would you distinguish between company names and names of people?

 

[tbrynard01]

What version of Excel are you running (please modify your account details)?
How would you distinguish between company names and names of people?

Office 365 There is really no specific way to distinguish between company names and people it seems very random (this is a new job for me) Not sure if that is helpful

 

[Joe4]

Office 365 There is really no specific way to distinguish between company names and people it seems very random (this is a new job for me) Not sure if that is helpful

Yes, that is going to be a tough one.

The thing I usually tell people is that Excel is not "magic". It is very literal. It can only do what you tell it.
So, if you can come up with "rules" and explain (just in plain English) exactly how it should work (how it can correctly identify names of people from companies), then there is a good chance we can do something with that. But if it is impossible to come up with any explanations on how to make that determination, it is probably going to be impossible to program (you usually cannot program that which cannot be clearly defined).

 

[tbrynard01]

Okay thank you. I was able to find a work around

 

[Joe4]

Okay thank you. I was able to find a work around

You are welcome.

I am curious, what was your workaround?
I ask because questions like this come up fairly often, so I am interested to hear how people end up handling it. It might someone down the road who has the same issue and comes across this thread (to see/hear how others handled this).

 

[tbrynard01]

I had another file with the donor ID so I did a VLOOKUP (the other file also had the combined name) on the combined name for the donor ID and was able to upload it into our CRM only using the donor ID