Thank you gor your reply. It's interesting to compare your solution with the
second one. Both work perfectly though. Much appreciated.

"Bob Phillips" wrote:

Iain,

This will get the position of the last space

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

The surname then just becomes

=MID(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))+1,99)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Iainkerr01" wrote in message
...
I have a spreadsheet column which stores client names in the format 'Mr &
Mrs
Fred Bloggs' or Mr Iain Kerr.

I want to be able to sort this in alphabetic order based on the Surname
only. Is there a multi function to determine where the first space
charactor
is from the Right Hand Side? This would give the the start position (-1)
of
the Surname.

I figured that if I can isolate that into a hidden column, then I can
easily
sort on it.