name last name order

huorsa

Board Regular
Joined
Feb 15, 2002
Messages
101
I have a column with a list of namen, starting by the person's name then last name and I want to order them by last name.

I have
Peter Wilson
Mark A. Church
Arnold Parker
Benjamin Tompson

I want this:
Church Mark A.
Parker Arnold
Tompson Benjamin
Wilson Peter

Any ideas????
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

huorsa

Board Regular
Joined
Feb 15, 2002
Messages
101
It works, just have a little problem, some people have two last name, per example.

John Smith Tuggle

and when I apply your formula it appears
Tuggle Jonh Smith
and it should be
Smith Tuggle John

How can nI fix this
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
On 2002-11-06 12:20, huorsa wrote:
It works, just have a little problem, some people have two last name, per example.

John Smith Tuggle

and when I apply your formula it appears
Tuggle Jonh Smith
and it should be
Smith Tuggle John

How can nI fix this

That's difficult, unless there is some reularity by which middle names can be distinguished from the two last name cases, such as: all middle names consist of a letter followed by a period and last names are fully written out.
 

huorsa

Board Regular
Joined
Feb 15, 2002
Messages
101

ADVERTISEMENT

I don't know if this makes it easier, suppose all names have 4 words and the last two words are the last name.

Peter John Smith tuggle
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
On 2002-11-06 12:55, huorsa wrote:
I don't know if this makes it easier, suppose all names have 4 words and the last two words are the last name.

Peter John Smith tuggle

=WMID(A1,3,2)&" "&WMID(A1,1,2)

or

=WMID(A1,3,2)&", "&WMID(A1,1,2)

if your entries that regular.
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi huorsa:

In addition to the formulations suggested by Aladin, try

=IF(ISERROR(FIND(".",A1,1)),RIGHT(A1,LEN(A1)-FIND(" ",A1,1))&" "&LEFT(A1,FIND(" ",A1,1)-1),RIGHT(A1,LEN(A1)-FIND(".",A1,1)-1)&" "&LEFT(A1,FIND(".",A1,1)))

This proposed formulation will handle, double last names, single last names, as well as middle initial in the name. See the worksheet simulation ...
Book1
ABCD
1PeterWilsonWilsonPeter
2MarkA.ChurchChurchMarkA.
3ArnoldParkerParkerArnold
4BenjaminThompsonThompsonBenjamin
5JohnSmithTuggleSmithTuggleJohn
Sheet4
</SPAN>

Regards!
Yogi Anand
 

Forum statistics

Threads
1,144,274
Messages
5,723,442
Members
422,497
Latest member
dougy99

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
Top