software

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 gt; 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,quot; quot;) 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

?ee??ezt ?rta:

gt; Please help I have a very long list of names all typed in one cell ie:
gt;
gt; Miss L Rayner
gt; Mr J C F Clark
gt; Mr P R Brown
gt; Mr D L P Race
gt; Ms Peal
gt;
gt; I need to sort this list by the last name.
gt;
gt; I can sort this via Data gt; Text to columns, but this give the surname in
gt; either column 3, 4 or 5... is there anyway to overcome this and sort all
gt; those after the last space?

Use this formula to get the last name in a separate column

=RIGHT(A1,LEN(A1)-FIND(quot;~quot;,SUBSTITUTE(A1,quot; quot;,quot;~quot;,LEN(A1)-LEN(SUBSTITUTE(A1,quot;
quot;,quot;quot;)))))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Teequot; gt; wrote in message
...
gt; Please help I have a very long list of names all typed in one cell ie:
gt;
gt; Miss L Rayner
gt; Mr J C F Clark
gt; Mr P R Brown
gt; Mr D L P Race
gt; Ms Peal
gt;
gt; I need to sort this list by the last name.
gt;
gt; I can sort this via Data gt; Text to columns, but this give the surname in
gt; either column 3, 4 or 5... is there anyway to overcome this and sort all
gt; 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.

quot;Teequot; gt; wrote in message
...
gt; Please help I have a very long list of names all typed in one cell ie:
gt;
gt; Miss L Rayner
gt; Mr J C F Clark
gt; Mr P R Brown
gt; Mr D L P Race
gt; Ms Peal
gt;
gt; I need to sort this list by the last name.
gt;
gt; I can sort this via Data gt; Text to columns, but this give the surname in
gt; either column 3, 4 or 5... is there anyway to overcome this and sort all
gt; 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?ob Phillips??ezt ?rta:

gt; Use this formula to get the last name in a separate column
gt;
gt; =RIGHT(A1,LEN(A1)-FIND(quot;~quot;,SUBSTITUTE(A1,quot; quot;,quot;~quot;,LEN(A1)-LEN(SUBSTITUTE(A1,quot;
gt; quot;,quot;quot;)))))
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;Teequot; gt; wrote in message
gt; ...
gt; gt; Please help I have a very long list of names all typed in one cell ie:
gt; gt;
gt; gt; Miss L Rayner
gt; gt; Mr J C F Clark
gt; gt; Mr P R Brown
gt; gt; Mr D L P Race
gt; gt; Ms Peal
gt; gt;
gt; gt; I need to sort this list by the last name.
gt; gt;
gt; gt; I can sort this via Data gt; Text to columns, but this give the surname in
gt; gt; either column 3, 4 or 5... is there anyway to overcome this and sort all
gt; gt; those after the last space?
gt;
gt;
gt;