sort help

[Martin ©¿©¬]

Hi
I have a spreadsheet with a list of names
Mr & Mrs E. McAllister
Mrs E. Hay
Rev. G. & Mr M. Farquhar
Mrs M. Russell
Mr & Mrs J. Cuthbert
Mr R. Cuthbert
Miss Judith Cuthbert
Mr & Mrs M. Weller
Mrs C. Doherty
Mr & Mrs J. Stewart
Mr W. Ridges
Mrs M. Bell
Mr & Mrs D. Bell
Mr & Mrs J. McMurran
Mrs A. Erskine
etc
etc

How do I sort by surname please?
Using 2007

 

[David Biddulph]

Make sure that the surname is in a separate column. Select all the relevant
columns before you sort.

 

[Gary''s Student]

http://groups.google.com/group/micr...583b2d?lnk=gst&q="last+word"#e381ee91e3583b2d

 

[Mike H]

Hi,

You need a helper column. Use this formula to extract the last name in to
another column

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

Select both columns and sort on the helper column which you can hide if you
want


Mike

 

[trip_to_tokyo]

Martin:-

I have just put up a file for you at:-

http://www.pierrefondes.com/

It is item number 35 towards the top of my home page.

Your data really needs to be reorganized as it is not really in a manageable
form.

However, for the Workbook I have uploaded, if you click in the drop down in
cell H 1 and select Sort A to Z you will get the people in the alpha order of
surname.

There are many ways this could be done.

If my comments have helped please hit Yes.

Thanks.

 

[Martin ©¿©¬]

Make sure that the surname is in a separate column. Select all the relevant
columns before you sort.

Hi David
I was hopeing to be able to sort without having to have/keep the
surname in a seperate column

 

[Clarity]

Hi,

The first thing you will need to do is create a "helper" column next to your
data. In this column you will need to extract the surname from the full name.
You can then sort by the surname in the "helper" column.

There are a couple ways to extract the surname.

Firstly you could use "text to columns". Given the text below you could
possibly use "." as the seperator.

Alternatively you could use formula to pull out the surname. Full name in A1.

=RIGHT(A1,(LEN(A1)-FIND(".",A1,4)-1))

One note of caution :- you will need to be careful of your full name format
as neither of the above will work with "Rev. G. & Mr M. Farquhar" as there
are several "."s.

Myles

 

[David Biddulph]

You're out of luck.
--
David Biddulph

Hi David
I was hopeing to be able to sort without having to have/keep the
surname in a seperate column

 

[Stefi]

Separate surname into an empty helper column with this formula:
=MID(A1,FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))+1,256)
and sort by the helper column!

--
Regards!
Stefi



„Martin ©¿©¬ @nohere.net†ezt írta:

 

[מיכ×ל (מיקי) ×בידן]

Consider retrieving the Surname into col. B with the proposed formula and
then sort, col. A+B, as per col. B
You can hide col. B whenever you feel like it.
http://img251.imageshack.us/img251/4248/nonamef.png
Micky

 

[Eva]

Insert the column, then the formula
=MID(A1,SEARCH(".",A1,1)+2,LEN(A1))
then sort it out
click yes if helped

 

[David Biddulph]

So for "Rev. G. & Mr M. Farquhar" you are treating the surname as "G. & Mr
M. Farquhar"
and for "Miss Judith Cuthbert" you get #VALUE!

But if there aren't too many of that type of exception it might make some
sense to process the entries your way and deal with the exceptions manually.

 

[Martin ©¿©¬]

Thanks for all your help
I have been off line for a few days
I will try your suggestions