Want to help a forum user?

I have a large document with a couple of hundred names, I want to put the names in alphabetical order by surname, unfortunately, the names have been entered with the first name in front, IE John Jobinson etc.

I put in a new column with the following formula
=RIGHT(C6,LEN(C6)-FIND(" ",C6))
which worked OK except where there is also a middle name, it then copies across the middle and last name.

Can anyone suggest a formula to copy across the surname only?

No, can't delete the middle name as this (apart from the employee number) is the only way to easily differentiate people with the same first and second name.
Select the header button for the surname column to select all the surnames, presuming first and middle name columns are to the right of surname and adjacent, click on sort acending button (the A to Z button) on the toolbar and select expand option when prompted this will sort all names and keep them in alphabetical order
 
Sponsored Links
I took it that he meant the first, surname and middle name was all in a single columm, which is why I suggested turning it into a CSV
 
Sponsored Links
Perhaps you could get them all to change their names by deed poll?
 
got a work around that works:

In your formula =RIGHT(C6,LEN(C6)-FIND(" ",C6)) replace with

=LEFT(C6,LEN(C6)-FIND(" ",C6)) this sorts out all the first and middle names, get the LEN of this and subtract it from the LEN of the complete name, this gives you the length of the surname, then RIGHT that amount of characters to get the surname alone, once you have first and middle names and surnames in seperate columns it's a matter of sorting alphabetically by surname.

Edit: I've found it worked to a limit, but have found the maths in the original formula is wrong, getting the length of the full name and subtracting the length of name up to the first space doesnt always work dependant on length of name and where the space is within it.

Its one of those strange maths problems similar to the brain teaser where
three people split a restaurant bill and the waiter takes his tip out and where is the missing pound?....

will have to think this one out and how to properly show the formula.

View media item 10887
 
The only progress I can make is doing two finds which gives some Value Errors which you can then sort by the IsError function but its getting long winded and certainly not a simple solution as requested.
 
The only progress I can make is doing two finds which gives some Value Errors which you can then sort by the IsError function but its getting long winded and certainly not a simple solution as requested.
Yeah that's what I was gonna say..... :confused: :D
 
Pips the resident Excel expert maybe he can find an easy solution?
 
Sponsored Links
Back
Top