Sort names excel 2007

  tonyq 12 Nov 12
Locked
Answered

Hi all, Is it possible to sort a list of names after a full stop using Excel 2007. e.g Mr A.Somebody I would like to sort the list of names using the surname,without having to place the surname in a different column, hope this makes sense!.

  hastelloy 12 Nov 12

It probably is possible though I'm afraid I don't know how. I suspect it would be simpler to have Somebody Mr A to sort.

  TonyV 12 Nov 12

marvin42

Or a separate column for the surname then sort that column by ascending order.

TonyV

  TonyV 12 Nov 12

marvin42

I know you say you would rather not use a separate column, but I can't see how you can sort by using part of a cells contents.

TonyV

  TonyV 12 Nov 12

In too much of a rush, the above two missives should be addressed to tonyq.

TonyV

  Woolwell 12 Nov 12

You can do it by using another column and by inserting a formula in that column and then sorting on it. The snag that you have is a variable surname length and you need to cater for mr and mrs and make sure that the dot always follows just the first initial.

With the first name in cell A1 then you can insert in a new column =MID(A1,FIND(".",A1)+1,12). This will return the surname in the new column up to a maximum of 12 letters in the surname which you can then sort on. If you have more than 12 letters then set the 12 to 25 or whatever.

I usually have a separate columns for titles, initials and surnames.

  tonyq 12 Nov 12

Thank you all for your replies. Woolwell,I did as you suggested, but having got the names in a new column,I am unable to sort them using "Sort and Filter A-Z"

  Woolwell 12 Nov 12

Select then entries in the new column, choose sort and filter, sort a to z, it should then ask if you want to expand the selection, select yes and it should sort.

  lotvic 12 Nov 12

Warning, do NOT have any blank columns or hidden columns or it gets messed up. I speak from experience with mailing list last Dec - on that list they've now all got the wrong phone numbers :( I had to start again with a backup copy.

  lotvic 12 Nov 12

Also should have asked, do your columns have Headings? (or does A1 contain a person - Mr A. Somebody)

  Woolwell 12 Nov 12

Immediate undo will put it back neat after a mess up. If worried make another copy first.

Advertisement

This thread is now locked and can not be replied to.

Should I upgrade to Windows 10? 8 reasons why you should upgrade to Windows 10... and 2 why you…

We are being sold the ability to spend money we don't have. And we love it

IKinema aims to banish droopy shoulders and wonky spines in animated CG characters

How to use Apple Music in the UK: Complete guide to Apple Music's features

We use cookies to provide you with a better experience. If you continue to use this site, we'll assume you're happy with this. Alternatively, click here to find out how to manage these cookies

hide cookie message