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

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also can you post some sample data along with expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Imajica]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also can you post some sample data along with expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Thank you for the info. I have updated my profile and will upload the rest shortly.

Michael

 

[Imajica]

The expected results columns have no formulas. They just show what I am expecting to happen. Ultimately, there may be multiple names in the middle. Could be 1, 2 or 3. I need one column to contain the last name, and the other column to contain everything else.

Book1
	A	B	C	D	E	F	G
1
2		Expected Results			What happens
3	Raw Data	Last Name	First Name		Last Name	First Name
4	John Smith	Smith	John		Smith	John
5	John Applebee Smith	Smith	John Applebee		Smith	John Applebee
6	John Michael Applebee Smith	Smith	John Michael Applebee		Smith	John Michael
Sheet1

Cell Formulas
Range	Formula
E4:E6	E4	=RIGHT(A4, LEN(A4) - SEARCH("#", SUBSTITUTE(A4," ", "#", LEN(A4) - LEN(SUBSTITUTE(A4, " ", "")))))
F4:F6	F4	=LEFT(A4, SEARCH(" ", A4) - 1)&" "&IFERROR(MID(A4,SEARCH(" ",A4,1)+1,SEARCH(" ",A4,SEARCH(" ",A4,1)+1)-SEARCH(" ",A4,1)),"")

 

[Joe4]

It looks like you are always assuming that the last name is just one word. But what about people with two word last names, like Jean-Claude Van Damme?
Or might you have people with things like "Jr", "Sr", or "III" after their last name?

This is the real challenge in working with names - there don't seem to be any hard-and-fast rules, and their are exceptions to almost every rule you can come up.
Both the first name and last name could have multiple words in it, and there isn't any real good way to come up with a rule to know exactly where to split it in every case.

 

[Imajica]

It looks like you are always assuming that the last name is just one word. But what about people with two word last names, like Jean-Claude Van Damme?
Or might you have people with things like "Jr", "Sr", or "III" after their last name?

This is the real challenge in working with names - there don't seem to be any hard-and-fast rules, and their are exceptions to almost every rule you can come up.
Both the first name and last name could have multiple words in it, and there isn't any real good way to come up with a rule to know exactly where to split it in every case.

I am working with a list of names from our cemetery. List of people who are interred and those who have plot reservations. There are no hyphenated last or first names as far as I know. Although, if the search function uses spaces to figure out the words, then a hyphen would not count as a space and would show up as one word. I also do not have any JR or SR or anything like that as a suffix to names. If there are any, there would be very few and I can always manually correct those that need it. There may be one or 2 exceptions, but I can deal with those manually if needed.

Ideally, the program that exports the info should be able to separate all this info as the data is entered as separate fields, but the program exports everything in one cell. The company that wrote the program won't change it.

So really, I just need to split off the last name, which I can already do. And I need everything except that last name to show up in another column.

 

[Fluff]

Thanks for that, how about for 365

Fluff.xlsm
	A	B	C
1
2		Expected Results
3	Raw Data	Last Name	First Name
4	John Smith	Smith	John
5	John Applebee Smith	Smith	John Applebee
6	John Michael Applebee Smith	Smith	John Michael Applebee
Details

Cell Formulas
Range	Formula
B4:B6	B4	=TAKE(TEXTSPLIT(A4," "),,-1)
C4:C6	C4	=TEXTJOIN(" ",,DROP(TEXTSPLIT(A4," "),,-1))

 

[Joe4]

There are no hyphenated last or first names as far as I know

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!).

For an entry in cell A4, put this formula in cell B4 to get the last name:

=TRIM(RIGHT(SUBSTITUTE(A4," ",REPT(" ",100)),100))

and then put this in C4 to get the first name (rest):

=TRIM(LEFT(A4,LEN(A4)-LEN(B4)))

 

[Imajica]

Thanks for that, how about for 365

Thanks, that does the trick.

What do you mean by "how about for 365"? I checked my profile and I checked off 365, 2021, which are the 2 versions I use. 365 at work and 2021 at home.

 

[Fluff]

That will only work in 365, which is why I said it, incase you tried to use it in 2021