How can I sort list of names by last name not first? 2

[Starrizzo]

I have a column of names but each cell has the first name and the last name in it. is there a way for me to sort the column by the last name not the first?

 

[gizmo1973]

Is there a split between first and last such as a space or a comma?
If so split the cells by rext to columns so you would then have a column with 1st name and a column with surname.
Then sort by surname

Regards, Phil.

M.U.F.C. Show your true support here:

http://www.shareholdersunited.org/join.php

"Shares not Shirts

 

[eyec]

not likely.

you will probably have to copy them to the next cell and edit the names.

OR

use the replcae option and put a comma between the first & last names, export it to a text file then re-import it as a csv file.

 

[jdhilljr]

If you have only 1st name and last name separated by a space this macro will do it.

Sub Macro1()
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("B1").Select
ActiveCell.FormulaR1C1 = "=FIND("" "",RC[1])"
Selection.AutoFill Destination:=Range("B1:B65000"), Type:=xlFillDefault
Range("A1").Select
ActiveCell.FormulaR1C1 = "=LEN(RC[2])"
Selection.AutoFill Destination:=Range("A1:A65000"), Type:=xlFillDefault
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
ActiveCell.FormulaR1C1 = "=RIGHT(RC[3],RC[1]-RC[2])"
Selection.AutoFill Destination:=Range("A1:A65000"), Type:=xlFillDefault
Range("A1:A3").Select
Range("A1").Select
Selection.Copy
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("A1:A65000"), Type:=xlFillDefault
Range("B1").Select
Selection.AutoFill Destination:=Range("B1:B65000"), Type:=xlFillDefault
Range("C1").Select
Selection.AutoFill Destination:=Range("C1:C65000"), Type:=xlFillDefault
Columns("A:A").Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Cells.Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Columns("A:C").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select

End Sub

Jim

 

[barbola]

If there is a space between the first and last name, you can use Data > Text to Columns then select "Delimited" and check off space and it will show you where it will separate the data into two separate columns.

If there are any names with more spaces, it will create extra columns so you will want to check all your data afterwards.



Thanks!
Barb E.

 

[gizmo1973]

Echo? lol

Regards, Phil.

M.U.F.C. Show your true support here:

http://www.shareholdersunited.org/join.php

"Shares not Shirts

 

[Starrizzo]

Thanks the delimited worked out just dandy!

 

[barbola]

i know i know. but i dont think the user would be able to find "rext to columns" on the menu.

Thanks!
Barb E.

 

[gizmo1973]

I realised that after I posted it!!!!!
Needs spellchecker on here!!!

Regards, Phil.

M.U.F.C. Show your true support here:

http://www.shareholdersunited.org/join.php

"Shares not Shirts