I have a spreadsheet (Excel 2003) in which column A contains a list of
names (known name + surname). In col B is the following formula:

=IF(ISBLANK(A4),"",CONCATENATE(RIGHT(A4,LEN(A4)-FIND(" ",A4)),", ",LEFT
(A4,FIND(" ",A4)-1)))

which turns 'Peter Smith' in col A into 'Smith, Peter' in col B -
enabling me to sort the list alphabetically by surname.

However, I've recently added some names to the list which have a title
e.g. Cllr Peter Smith. Now the formula results in 'Peter Smith, Cllr'
which mucks up the alphabetic sorting!

I can see the problem - the find function is finding the first space
rather than the second when there are two. If only there were a way of
searching right to left but, if there is, I haven't been clever enough
to find it yet. Or perhaps there is another way altogether to tackle
this issue.

I'd be very grateful if anyone has any bright ideas. Thanks.

V