How do I make surnames appear alphabetically in Excel spreadsheets?

Amanda Bainbridge wrote:
How do I make surnames appear alphabetically in Excel spreadsheets?


If you have a column of the names, highlight them all and then click ToolsSort
and work your way through the panels. Excel will then sort everything
alphabetically for you.

If the column of names is followed by other columns of corresponding data,
you'll need to highlight all the columns so when the names are sorted into order
their corresponding data will move with them.

Good luck...

Bill

I assume you have names such as Amanda Bainbridge in one cell. You need to
extract the surname to sort on. In a helper column add this formula

=RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1,"
","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

copy this for all names then sort all columns using this helper column as
the key.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Amanda Bainbridge" <Amanda wrote in
message ...
How do I make surnames appear alphabetically in Excel spreadsheets?

Bob,

Having a bit of trouble with this - it pastes into Excel in two cells and
when I try to concatenate them, I keep getting #VALUE!
I've looked repeatedly at what I have, but can't seem to find the error!

Pete



"Bob Phillips" wrote:

I assume you have names such as Amanda Bainbridge in one cell. You need to
extract the surname to sort on. In a helper column add this formula

=RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1,"
","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

copy this for all names then sort all columns using this helper column as
the key.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Amanda Bainbridge" <Amanda wrote in
message ...
How do I make surnames appear alphabetically in Excel spreadsheets?

Try copying from the newsgroup post, but paste it into the formula bar--not
directly into the cell.

=RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1," ",
"^^",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

(I broke the line feed at a position that won't hurt when you copy|paste.)

Peter Rooney wrote:

Bob,

Having a bit of trouble with this - it pastes into Excel in two cells and
when I try to concatenate them, I keep getting #VALUE!
I've looked repeatedly at what I have, but can't seem to find the error!

Pete

"Bob Phillips" wrote:

I assume you have names such as Amanda Bainbridge in one cell. You need to
extract the surname to sort on. In a helper column add this formula

=RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1,"
","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

copy this for all names then sort all columns using this helper column as
the key.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Amanda Bainbridge" <Amanda wrote in
message ...
How do I make surnames appear alphabetically in Excel spreadsheets?




--

Dave Peterson

Thanks, Dave! :-)

Pete



"Dave Peterson" wrote:

Try copying from the newsgroup post, but paste it into the formula bar--not
directly into the cell.

=RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1," ",
"^^",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

(I broke the line feed at a position that won't hurt when you copy|paste.)

Peter Rooney wrote:

Bob,

Having a bit of trouble with this - it pastes into Excel in two cells and
when I try to concatenate them, I keep getting #VALUE!
I've looked repeatedly at what I have, but can't seem to find the error!

Pete

"Bob Phillips" wrote:

I assume you have names such as Amanda Bainbridge in one cell. You need to
extract the surname to sort on. In a helper column add this formula

=RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1,"
","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

copy this for all names then sort all columns using this helper column as
the key.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Amanda Bainbridge" <Amanda wrote in
message ...
How do I make surnames appear alphabetically in Excel spreadsheets?




--

Dave Peterson