Help find First and Last Name Only

Grimm127

Board Regular
Joined
Aug 17, 2011
Messages
132
Hi Excel Gurus!

I am in a bit of a puzzle. I have a list of names. Which has their first name, middle name, suffix, last name pretty much everything you can throw at it.
I am trying to compile only just their first and last names. Its over 5k rows and so far I tried a text to columns which results in about 6 columns. Sometimes the last name is spread out due to it being ex: De La Pura.
Sometimes the middle name is just an initail or the suffix is jr or III etc.

Do you have any ideas on what can be created to be able to run through this list and just give me the first and last name if possible?
I am stuck.

Thanks!
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Its impossible im afraid. I wouldnt even know De La Pura was a last name or that just Pura was the last name so a machine isnt going to know. There is no logic to create what you ask for.
 
Upvote 0
This is a question that gets asked quite often, and really there is no perfect solution to this due to the unpredicatable nature of the data.
You can have multi-word first names like "Betty Sue" or multi-word last names (like you have shown), and that creates a big problem, as in order to program anything, you need to first determine the "rules". But if there are no "hard-and-fast" rules, what can you program to? Excel is very literal - it can only do what you tell it to do, it cannot think for itself.

As such, in cases like these, what you usually aim to do is to create a solution that works for the majority of cases (if you can hit 90-95%, you are doing pretty good).
I don't think there is any way around the fact that there will be exceptions that do not work, and would will need to manually review the results and make updates where necessary.
 
Upvote 0
By the way with one word First and Last names its very possible so may shorten your list down?
 
Upvote 0
Thanks both of you. I thought it was an Impossible task to a certain extent based on what I am currently processing. I think I am going to try a different approach to see if I can somehow bridge the gap because far I am in the 50% range and it needs to be at least 85%. Blessings!
 
Upvote 0
Thanks both of you. I thought it was an Impossible task to a certain extent based on what I am currently processing. I think I am going to try a different approach to see if I can somehow bridge the gap because far I am in the 50% range and it needs to be at least 85%. Blessings!
You are welcome.

I would suggest just trying to come up with the "rules" (without worrying about how to do them in Excel at first). Then, see if you can program those rules, and if not, post back here for help.

For example, if there is just one space in your whole entry, your know the format is "FIRST LAST". That one is easy enough to program.
But see if you can identify any other patterns, or come up with any other rules.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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
Back
Top