Find And Display Surname

[robzrob]

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.

 

[Ron Rosenfeld]

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

 

[robzrob]

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.

 

[Rick Rothstein \(MVP - VB\)]

I'm sure there's an easy way to do this, but I can't find it! I've

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

 

[Ron Rosenfeld]

=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

 

[Ragdyer]

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

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

 

[Rick Rothstein \(MVP - VB\)]

=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

 

[Rick Rothstein \(MVP - VB\)]

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]

I liked it too, the first time I saw it.

Wish I could remember who to credit it to.

 

[robzrob]

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



- Show quoted text -

Thanks all.