Help find First and Last Name Only

[Grimm127]

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!

 

[steve the fish]

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.

 

[Joe4]

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.

 

[steve the fish]

By the way with one word First and Last names its very possible so may shorten your list down?

 

[Grimm127]

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!

 

[Joe4]

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.