sort help

  • Thread starter Thread starter Martin ©¿©¬
  • Start date Start date
M

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
 
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
 
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.
 
Make sure that the surname is in a separate column. Select all the relevant
columns before you sort.
Click to expand...

Hi David
I was hopeing to be able to sort without having to have/keep the
surname in a seperate column
 
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
 
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:
 
Insert the column, then the formula
=MID(A1,SEARCH(".",A1,1)+2,LEN(A1))
then sort it out
click yes if helped
 
Thanks for all your help
I have been off line for a few days
I will try your suggestions
 
Back
Top