Extracting Last names only from a cell of contacts

[linaeum66]

<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@




[TABLE="width: 0"]
<colgroup><col style="width: 100px"><col width="100"><col width="100"></colgroup><tbody>[TR]
[TD]Full name[/TD]
[TD][/TD]
[TD]Last Name[/TD]
[/TR]
[TR]
[TD]John Doe[/TD]
[TD][/TD]
[TD]Doe[/TD]
[/TR]
[TR]
[TD]Doe, John[/TD]
[TD][/TD]
[TD]Doe[/TD]
[/TR]
[TR]
[TD]John D. Doe[/TD]
[TD][/TD]
[TD]Doe[/TD]
[/TR]
[TR]
[TD]John Doe MD[/TD]
[TD][/TD]
[TD]Doe[/TD]
[/TR]
</tbody>[/TABLE]

 

[Gerald Higgins]

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
?

 

[linaeum66]

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

 

[Gerald Higgins]

OK, well if you can answer my questions, maybe I or someone else can do that.

 

[linaeum66]

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.

 

[linaeum66]

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.

 

[Gerald Higgins]

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.

=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.

 

[István Hirsch]

Another version:

Excel Workbook
	A	B
1	Full name	Last name
2	John Doe	Doe
3	Doe, John	Doe
4	John D. Doe	Doe
5	John Doe MD	Doe
6	John Doe MA	Doe
7	John Doe PhD	Doe
Sheet1

 

[jtakw]

Hi,

Yet another option:

Book1
	A	B	C
1	Full name		Last Name
2	John Doe		Doe
3	Doe, John		Doe
4	John D. Doe		Doe
5	John Doe MD		Doe
6	John D. Doe MA		Doe
7	John Doe PhD		Doe
8	John Doe Jr.	???
Sheet43

Cell Formulas
Range	Formula
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))))

 

[Vikas Kumar]

May be helpful ..........

=MID(A2,FIND("Doe",A2),3)