Sort names excel 2007

  tonyq 11:20 AM 12 Nov 12

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


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


  TonyV 14:00 PM 12 Nov 12


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 PM 12 Nov 12

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


  Woolwell 14:39 PM 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 18:16 PM 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 18:24 PM 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 18:51 PM 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 18:54 PM 12 Nov 12

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

  Woolwell 19:01 PM 12 Nov 12

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.

Best Cyber Monday deals 2015 live blog: Best UK Black Friday weekend deals & best UK Cyber Monday…

1995-2015: How technology has changed the world in 20 years

The best Black Friday Deals for designers and artists – updated

20 Mac Power User Tricks... That You Didn't Know