Help extracting part of a string

[Ken Mahler]

Greetings,

I have a name value in A1,eg: Mr. Joe Smith. I need a function/formula that
will find the space just before the last name -- the 1st space character
from the right -- and extract everyting to the right of it so I get just the
last name in cell A2.

TIA

Ken

 

[Bob Phillips]

Hi Ken,

This gets that name

=MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,32)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Harlan Grove]

This gets that name

=MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,32)

...

It does?

With the OP's example of

Mr. Joe Smith

in cell A1 (note I dropped the period immediately after Smith - I'll stand by
the assumption it's not in A1) your formula returns

Joe Smith

It also would have problems with hyphenated surnames for people with non-Western
European ancestors, which could occasionally exceed 32 characters.

Here's a correct and culturally inclusive alternative assuming that there'd be
nothing after the last name

=MID(TRIM(A15),MAX(IF(MID(TRIM(A15),ROW(INDIRECT("1:"&LEN(A15))),1)=" ",
ROW(INDIRECT("1:"&LEN(A15)))))+1,LEN(A15))

It's an array formula. It's also rather redundant. A nonarray formula
alternative that's also somewhat redundant would be

=MID(A15,FIND(CHAR(127),SUBSTITUTE(TRIM(A15)," ",CHAR(127),
LEN(TRIM(A15))-LEN(SUBSTITUTE(TRIM(A15)," ",""))))+1,LEN(A15))

However, if the OP needs to do this sort of thing often and occasionally needs
greater flexibility (such as handling generational qualifiers like Jr. or
multiple word last names like de la Cruz), then see the following thread.

http://www.google.com/groups?threadm=4a0901c2bb1b$0a81e030$8ef82ecf@TK2MSFTNGXA04

 

[Harlan Grove]

...
..

It does?

...

Yes, it does! I screwed up my test. However, it wouldn't handle

Mr. John Q. Public

 

[Bob Phillips]

...
..

Yes, it does! I screwed up my test. However, it wouldn't handle

Tsk, tsk!

Mr. John Q. Public

I know Harlan, but he did ask for a specific, not a generic, that is what I
gave hime.

Love the RegExp solution !


Bob

 

[Harlan Grove]

...
...

I know Harlan, but he did ask for a specific, not a generic, that is what I
gave hime.

...

Not really. From the original post:

'I need a function/formula that will find the space just before the last name
--the 1st space character from the right -- and extract everyting to the right
of it so I get just the last name in cell A2."

The OP did provide an example, but most OP's don't provide realistic examples in
their original posts. If an OP provides a sample and specs, I tend to follow the
specs and ignore the sample. Personal quirkiness, I suppose.

 

[Ron Rosenfeld]

Greetings,

I have a name value in A1,eg: Mr. Joe Smith. I need a function/formula that
will find the space just before the last name -- the 1st space character
from the right -- and extract everyting to the right of it so I get just the
last name in cell A2.

TIA

Ken

=RIGHT(TRIM(A1),LEN(TRIM(A1))-FIND("~",SUBSTITUTE(
TRIM(A1)," ","~",LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ","")))))


--ron