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.
|