Want to help a forum user?

[tim west]

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

 

[disinfo]

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

 

[tim west]

Aha

 

[disinfo]

Aha

Now go and associate more words!!!!!!

 

[Blasphemous]

Good....errrm where's my thanks for restarting your thread?

Thanks, you want thanks...

Oh, OK then! Th, Tha, Than, Thank you Blas

You're we...wel...welc...welco.....welcome!

You forgot "Oh mighty one!"

Oh I stopped my people having to call me that a while back Secures..... I allow them to call me just plain old 'Sir' now.

Anyway, sorry for interrupting the thread.... sincerely.

 

[tim west]

Perhaps you could get them all to change their names by deed poll?

 

[Johnmelad502]

Quick and dirty way would be to copy to a text file, replace the spaces with commas, and reopen in Excel as a CSV.

Too many names and I am after a fix that would be quick.

 

[tim west]

Quick and dirty way would be to copy to a text file, replace the spaces with commas, and reopen in Excel as a CSV.

Too many names and I am after a fix that would be quick.

Sounds a very quick fix to me, you can use replace to do it, or were you thinking you'd have to do it manually for each name?

 

[tim west]

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

 

[Blasphemous]

Hey! You're canny clever you are Tim.

 

[tim west]

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.

 

[Blasphemous]

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

 

[tim west]

Pips the resident Excel expert maybe he can find an easy solution?