sort names

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Please help I have a very long list of names all typed in one cell ie:

Miss L Rayner
Mr J C F Clark
Mr P R Brown
Mr D L P Race
Ms Peal

I need to sort this list by the last name.

I can sort this via Data > Text to columns, but this give the surname in
either column 3, 4 or 5... is there anyway to overcome this and sort all
those after the last space?
 
Assume the names are in column A, with an UDF:

=MID(A1,FindRev(A1," ")+1,255)

Public Function FindRev(StrtoSearch As String, StrSearchedFor As String) As
Long
FindRev = 0
On Error Resume Next
FindRev = InStrRev(StrtoSearch, StrSearchedFor)
End Function

Regards,
Stefi

„Tee†ezt írta:
 
Use this formula to get the last name in a separate column

=RIGHT(A1,LEN(A1)-FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1,"
","")))))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
select the name column then \Data\Text to Columns delimited by space to
blank columns (splits into the various parts) then Data\Sort on last name.
Remove the no-longer needed columns, but might consider keeping the last
name as a hidden column for future resorting.
 
Tricky, I like it! But Microsoft should make things easier by introducing VB
InStrRev as a worksheet function!

Regards,
Stefi


„Bob Phillips†ezt írta:
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top