sorting by last name

Richard Schollar

MrExcel MVP

Joined

Apr 19, 2005

Messages

23,707

• Aug 4, 2010

• #2

Hi

You need to split the names out and then sort by a column just containing the last name eg in an adjacent column use the formula:

=REPLACE(A1,1,FIND(" ",A1&" "),"")

to extract just the last name and then select both original column and this column and sort based on this new formula column.

 

Upvote 0

Z

zcraigs

New Member

Joined

Jul 9, 2010

Messages

5

• Aug 4, 2010

• #3

Worked. thanks!

 

Upvote 0

P

Papi

Well-known Member

Joined

May 22, 2007

Messages

1,592

• Sep 3, 2010

• #4

Great stuff. What would a person do if they also have names with middle intiails, middle names or last names that would have things like Smith-Jones.

 

Upvote 0

Richard Schollar

MrExcel MVP

Joined

Apr 19, 2005

Messages

23,707

• Sep 3, 2010

• #5

Assuming it was structured like this in the name column:

Robert James Harris
Eileen Sue Smith-Jones
John Smith

Then you can get the last name only using:

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

 

Upvote 0

Burrgogi

Active Member

Joined

Nov 3, 2005

Messages

495

Office Version

1. 2010

Platform

1. Windows

• Sep 3, 2010

• #6

Papi said:

Great stuff. What would a person do if they also have names with middle intiails, middle names or last names that would have things like Smith-Jones.

Click to expand...

I actually saw a custom macro written just for that type of situation. I cannot remember which website I saw it on... but it can certainly be done with a VBA script.

Richard,
That is really kind of neat! What does the 'REPT' function do? (does it stand for repeat?)

 

Upvote 0

P

Papi

Well-known Member

Joined

May 22, 2007

Messages

1,592

• Sep 3, 2010

• #7

Hello Richard,

Thanks so much. If a person wanted to extract the middle name only how would the affect of sometimes three being an initial with or without a period or a full middle name instead?

 

Upvote 0

Burrgogi

Active Member

Joined

Nov 3, 2005

Messages

495

Office Version

1. 2010

Platform

1. Windows

• Sep 3, 2010

• #8

Papi said:

Hello Richard,

Thanks so much. If a person wanted to extract the middle name only how would the affect of sometimes three being an initial with or without a period or a full middle name instead?

Click to expand...

Using a variation of his previous formula, copy & paste this into an empty cell:

=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",100)),100,100))

The only situation where this will not work is if your workbook contains a list of names that don't have a middle initial. (e.g. John Doe). In such a case, it would treat "Doe" as the middle name - which you don't want. Until Richard replies back to this thread, this is the quickest solution I have for you.

 

Upvote 0

P

Papi

Well-known Member

Joined

May 22, 2007

Messages

1,592

• Sep 3, 2010

• #9

Hello Burrgogo,

Thanks. The list has many variants i.e. some without middle names, initials with or without periods etc.

 

Upvote 0

Burrgogi

Active Member

Joined

Nov 3, 2005

Messages

495

Office Version

1. 2010

Platform

1. Windows

• Sep 3, 2010

• #10

Papi said:

Hello Burrgogo,

Thanks. The list has many variants i.e. some without middle names, initials with or without periods etc.

Click to expand...

The whole premise is sort of odd to me. Who sorts by middle name anyway? In most situations I've come across, people want the list sort either by first or last name - rarely ever the middle name or initial. Is there something additional you need to after the sorting aspect? Can you give us more details?


At any rate, the function I gave you above will work regardless of whether the middle name (or initial) has a period. In your case, since the list contains a mixed bag of names with & without middle names, I think the best course is to use a VBA script.

 

Upvote 0