Extracting Last names only from a cell of contacts

linaeum66

New Member
Joined
Jul 2, 2017
Messages
25
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>Hello, I am looking for a formula to convert a full name in the various formats seen below and convert them all to a consistent "last name only" cell in an adjacent column. I've been trying myself for a while, but am not making much progress. Does anyone know how to do this?
Thanks so much@:)




Full nameLast Name
John DoeDoe
Doe, JohnDoe
John D. DoeDoe
John Doe MDDoe

<colgroup><col style="width: 100px"><col width="100"><col width="100"></colgroup><tbody>
</tbody>
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Hi, this general type of request is asked very often on this board, have you tried searching ?

Dealing with "John Doe" and "John D. Doe" will be reasonably straightforward.

Dealing with "Doe, John" - what rule can we use to determine that the required text is at the beginning ?
The rule COULD be that the text contains a "," character, which will be easy to deal with, but maybe it's more complicated than that.
What do you think ?

Dealing with "John Doe MD" - this is easy IF "MD" is the only possible suffix to ignore.
But maybe it's more complicated if there are other suffixes, such as "MA" or "(Retired)" or whatever.
What do you think ?

EDIT TO ADD
Might you also have examples such as
"John D.Doe" (no space between "." and "D")
"John Doe Smith", where you want to return Doe Smith
?
 
Last edited:
Upvote 0
Thanks for the quick reply. I have been doing some searching, and have found out how to do some individual cases, however I was hoping someone could suggest a single formula that could handle all 4 instances I mentioned
 
Upvote 0
sorry about that,
I think the presence of a comma would work as signifier the it is in last, first order. As far as suffixes, I would only need to remove MD, MA, and PhD.
 
Upvote 0
sorry about that,
I think the presence of a comma would work as signifier the it is in last, first order. As far as suffixes, I would only need to remove MD, MA, and PhD.
 
Upvote 0
Here's one solution, it's not particularly elegant but it does work for the 4 examples you provided.

There are probably better ways of doing this.

Code:
=IF(ISERROR(FIND(".",A1)),IF(ISERROR(FIND(",",A1)),IF(OR(RIGHT(A1,2)="MD",RIGHT(A1,2)="MA"),MID(A1,FIND(" ",A1,1)+1,LEN(A1)-3-FIND(" ",A1,1)),IF(RIGHT(A1,3)="PhD",MID(A1,FIND(" ",A1,1)+1,LEN(A1)-4-FIND(" ",A1,1)),RIGHT(A1,LEN(A1)-FIND(" ",A1,1)))),LEFT(A1,FIND(",",A1,1)-1)),MID(A1,FIND(".",A1)+2,200))

And with this type of problem, I expect you may well find more subtle variations of source data patterns, which this formula may not deal with correctly.
 
Upvote 0
Another version:

Excel Workbook
AB
1Full nameLast name
2John DoeDoe
3Doe, JohnDoe
4John D. DoeDoe
5John Doe MDDoe
6John Doe MADoe
7John Doe PhDDoe
Sheet1
 
Upvote 0
Hi,

Yet another option:


Book1
ABC
1Full nameLast Name
2John DoeDoe
3Doe, JohnDoe
4John D. DoeDoe
5John Doe MDDoe
6John D. Doe MADoe
7John Doe PhDDoe
8John Doe Jr.???
Sheet43
Cell Formulas
RangeFormula
C2=IF(ISNUMBER(FIND(",",A2)),LEFT(A2,FIND(",",A2)-1),IF(OR(RIGHT(A2,2)={"MD","MA"},RIGHT(A2,3)="PhD"),TRIM(LEFT(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),200),100)),TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100))))
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

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