Sorting 2 names in one column

H

Hides_Twins

help!

I have a column which lists the firstname, a space, then the surname.
ie. Joe Bloggs

I need to sort by SURNAME, then by firstname.

Can this be done without separating the names into different cells?
 
S

Scoops

Hides_Twins said:
help!

I have a column which lists the firstname, a space, then the surname.
ie. Joe Bloggs

I need to sort by SURNAME, then by firstname.

Can this be done without separating the names into different cells?
Click to expand...

Hi Hides_Twins

Why can't you split the names into two different columns? Your Excel
life would be much, much simpler if you did.

Regards

Steve
 
S

starguy

suppose your data starts from A1, try the following procedure.

in B1 put =MID(A1,FIND(" ",A1)+1,100) and copy down
in C1 put =MID(A1,1,FIND(" ",A1)-1) and copy down
select both columns (col B and C) and sort it first by col B and then
by col C.
then in col D enter following function.
=C1&" "&B1 and copy down

and you are done
hope this will serve your purpose
 
B

bman342

I think the easiest way would be to convert the text to columns (on the menu
bar: Data > Text to Columns. Use Space as the delimiter). Then do your sorts.

If it's just firstname and surname you're OK. But if there are middle
initials, or suffixes (Jr., III, etc.) it becomes problematic any way.
 
Top