Sorting names in Excel

[spotit3]

I have a list of names in an Excel sheet. Unfortunately they have titles i.e. Mr, Mrs etc.
Is there any way I can separate the titles from the name to allow me to sort the names into alphabetical order. I can't go over each one as there are more than 500 in the list. Any advice would be much appreciated.

[Pigletion]

I have found a fudge. If you ctrl+f for find and replace, then type Mr/Mrs or whatever and replace with a space. Be aware that if you do Mr first, you will take the Mr out of the Mrs to leave an S, so better do Mrs first (if that makes sense). You can do replace all, so will only have to do it for as many titles as you have. (Hopefully it is Mr. so you won't take Mr out of any names... ).

Then Sort A to Z. Can do this by right-clicking on cell and going to Sort. Or Sort and Filter button on Menu bar.

Hope that works... There is probably a better way, but I can't find it in the Sort menu..

[1rovert]

How about putting the Mr or Mrs after the name?

[Yorky Lass]

Highlight the column with the names in and select Data then select text to column. You will need two blank columns at the side of the one with the names in, presuming the list is like Mr John Smith. Click next then click finish. highlight the three new columns and select Data then Sort. Select the column the surname is in and click ok.

Hope that helps, It should make more sense as you do it.

[Pigletion]

Yes! Thank you!

I knew you could do it when importing, but have never noticed that before for data you already have.

Thanks Yorky Lass

[scotman]

Assuming your data is in a single column.

First copy the data to another new column so that you have backup if anything goes wrong.

Then use Find & Replace function to replace Mr or Mrs etc with nothing. This will give you a column of data which is only the surnames.
You can then sort using this column.