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.

Find And Display Surname
 

On Sat, 24 May 2008 12:03:23 -0700 (PDT), robzrob wrote:

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)

--ron

Find And Display Surname
 

On May 24, 8:40*pm, Ron Rosenfeld wrote:
On Sat, 24 May 2008 12:03:23 -0700 (PDT), robzrob wrote:
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)

--ron

Excellent, Ron, thank you.

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

Find And Display Surname
 

On Sun, 25 May 2008 15:10:29 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

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

If you make the ROW references absolute, then you'll be able to copy the
formula by dragging:

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

--ron

Find And Display Surname
 

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

Find And Display Surname
 

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

If you make the ROW references absolute, then you'll be able to copy the
formula by dragging:

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

Good point.

Rick

Find And Display Surname
 

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

Find And Display Surname
 

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

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.