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
Contact Forum Editor

Send an email to our Forum Editor:


PLEASE NOTE: Your name is used only to let the Forum Editor know who sent the message. Both your name and email address will not be used for any other purpose.

Tech Helproom


It's free to register, to post a question or to start / join a discussion


 

Sort names excel 2007


tonyq
Resolved

Likes # 0

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!.

Like this post
marvin42

Likes # 0

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.

Like this post
TonyV

Likes # 0

marvin42

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

TonyV

Like this post
TonyV

Likes # 0

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

Like this post
TonyV

Likes # 0

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

TonyV

Like this post
Woolwell

Likes # 0

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.

Like this post
tonyq

Likes # 0

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"

Like this post
Woolwell

Likes # 0

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.

Like this post
lotvic

Likes # 0

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.

Like this post
lotvic

Likes # 0

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

Like this post
Woolwell

Likes # 0

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

Like this post

Reply to this topic

This thread has been locked.



IDG UK Sites

The 30 best TV shows on Netflix UK: Our pick of the best programmes you can watch right now

IDG UK Sites

Nostalgia time: Top 10 best selling mobile phones in history

IDG UK Sites

VFX Emmy: Game of Thrones work garners gong for Rodeo FX

IDG UK Sites

Apple 13-inch MacBook Pro with Retina review (2.6GHz, 128GB, mid-2014)