|
Posted to microsoft.public.excel.worksheet.functions
|
|
|
Find And Display Surname
On May 26, 4:45*pm, "Ragdyer" wrote:
I liked it too, the first time I saw it.
Wish I could remember who to credit it to.
--
Regards,
RD
---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Rick Rothstein (MVP - VB)" wrote in
. ..
I like it! One function call less than mine and it still doesn't error out
if the reference cell is empty. Of course, if the last word is longer than
99 characters...<g
Rick
"Ragdyer" wrote in message
...
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
. ..
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- Hide quoted text -
- Show quoted text -
Thanks all.
|