Find And Display Surname
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
|