Sort names excel 2007

  tonyq 11:20 12 Nov 2012

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 13:39 12 Nov 2012

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 13:58 12 Nov 2012


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


  TonyV 14:00 12 Nov 2012


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 14:01 12 Nov 2012

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


  Woolwell 14:39 12 Nov 2012

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 18:16 12 Nov 2012

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 18:24 12 Nov 2012

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 18:51 12 Nov 2012

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 18:54 12 Nov 2012

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

  Woolwell 19:01 12 Nov 2012

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

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

Elsewhere on IDG sites

Best Amazon Echo: What’s the best Alexa speaker?

Kano Computer Kit Complete review: A fun DIY 'laptop' that teaches kids to code

Best pro photo editors for Mac 2018

TV & streaming : comment regarder les Jeux olympiques d’hiver 2018 ?