sort names

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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?
>
>
>