And an even *shorter, non-array* approach:

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Rick Rothstein (MVP - VB)" wrote in
message ...
I'm sure there's an easy way to do this, but I can't find it! I've
got names in cells like this: MRS EDNA JOAN PASCOE (could be any
number of first and middle names). In other corresponding cells I
want to display only the surname.

This formula will return the last word in a string:

=MID(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),
LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,255)

For those who might be interested, below is a shorter array-entered**
formula that does the same thing. It uses less function calls and does not
produce an error when the referenced cell is empty.

=MID(A1,MAX((MID(A1,ROW(1:255),1)=" ")*ROW(1:255))+1,255)

** For the archives: Use Ctrl+Shift+Enter to commit this formula, not
Enter
by itself.

Rick