How to split last name from multiple first/middle names

[Imajica]

I have a report that I need to be able to do a name split on. The data is not consistent.
- It will contain salutation (could be Mrs. or Mrs. or Mr. and Mrs. or Dr. and Mrs. or Dr. and Dr. etc)
- The name part can either be:
-- First name + last name
-- First name + middle name (or initial) + last name
-- first name + 2 middle names + last name

I need a way to split this off...

I do not need the salutations at all. So I found it easiest to highlight the column, do a control-F and choose replace. Then replace Mr. and all that with nothing. That solves that problem.

For the last name I use the following formula:
=RIGHT(E2, LEN(E2) - SEARCH("#", SUBSTITUTE(E2," ", "#", LEN(E2) - LEN(SUBSTITUTE(E2, " ", "")))))

For the first name only or first name and middle name I use:
=LEFT(E2, SEARCH(" ", E2) - 1)&" "&IFERROR(MID(E2,SEARCH(" ",E2,1)+1,SEARCH(" ",E2,SEARCH(" ",E2,1)+1)-SEARCH(" ",E2,1)),"")


Everything works perfectly except when there is a 2nd middle name. That simply gets removed and won't be included.

What formula could I use that would include the 2nd middle name? I was thinking of a formula that would include everything except the last name. So if there are 2 or 3 or 4 middle names, they would all be included. The formulas above i got from searching the net for help, but I have not been able to figure out the multiple middle name problem.

Thanks,
Michael

 

[Imajica]

That wasn't the point I was making in my example. The last name in my example is two words "Van Damme". It looks like you are always assuming a one-word last name, and I am telling you that is not always the case. There are people with two word last names (without a hyphen in between).

Regardless, if you realize that no solution is foolproof, and you are simply looking for formulas that will work most of the time, then you could do something like this, if you do not have access to the TEXTSPLIT function yet (I am on 365, but sadly don't have it on my work computer yet - I really want it!).

I do realize that there is no foolproof solution. Based on what I know of my data, if there are any multiple-worded last names without a hyphen (ex. Van Damme) I will have to handle those manually. I do not believe there are, but I could be wrong. It will have to be a chance I will take. The report needs to be printed for the groundskeepers working at the cemetery who do not have access to a computer. The printout does not need to be 100% accurate, so the quick and dirty way I need will work.

I tested out Fluff's solution because it was the one I saw first and the TEXTSPLIT does work. I did not realize that some functions would not work on all versions of Excel, let alone not be the same on different versions of 365 for example.

 

[Joe4]

I do realize that there is no foolproof solution. Based on what I know of my data, if there are any multiple-worded last names without a hyphen (ex. Van Damme) I will have to handle those manually. I do not believe there are, but I could be wrong. It will have to be a chance I will take. The report needs to be printed for the groundskeepers working at the cemetery who do not have access to a computer. The printout does not need to be 100% accurate, so the quick and dirty way I need will work.

I tested out Fluff's solution because it was the one I saw first and the TEXTSPLIT does work. I did not realize that some functions would not work on all versions of Excel, let alone not be the same on different versions of 365 for example.

That's fine. You have a few different solutions. TEXTSPLIT should work if you have 365 and all the updates (the one that includes the new TEXTSPLIT function).
My solution should work on other/older versions of Excel (as well as 365).