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:

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?

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)

"Tee" wrote in message
...
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?

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.

"Tee" wrote in message
...
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?

Tricky, I like it! But Microsoft should make things easier by introducing VB
InStrRev as a worksheet function!

Regards,
Stefi


€žBob Phillips€� 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)

"Tee" wrote in message
...
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?