How do I make surnames appear alphabetically in Excel spreadsheets?
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 Tools>Sort
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 [email protected]> wrote in
message news:[email protected]...
> 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 [email protected]> wrote in
> message news:[email protected]...
> > 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 [email protected]> wrote in
> > message news:[email protected]...
> > > 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 [email protected]> wrote in
> > > message news:[email protected]...
> > > > How do I make surnames appear alphabetically in Excel spreadsheets?
> > >
> > >
> > >
>
> --
>
> Dave Peterson
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks