Column Text Split

tbrynard01

Board Regular
Joined
Sep 20, 2017
Messages
109
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

kweaver

Well-known Member
Joined
May 8, 2018
Messages
2,134
Office Version
  1. 365
  2. 2010
What version of Excel are you running (please modify your account details)?
How would you distinguish between company names and names of people?
 

tbrynard01

Board Regular
Joined
Sep 20, 2017
Messages
109
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,028
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Sep 20, 2017
Messages
109
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,028
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Sep 20, 2017
Messages
109
Office Version
  1. 365
Platform
  1. Windows
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
 

Forum statistics

Threads
1,147,499
Messages
5,741,506
Members
423,663
Latest member
kaveh87rsh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top