Help extracting part of a string

  • Thread starter Thread starter Ken Mahler
  • Start date Start date
K

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
 
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)
 
This gets that name

=MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,32)
Click to expand...
...

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 said:
...
..

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

Tsk, tsk!
Mr. John Q. Public
Click to expand...

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

Love the RegExp solution !


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

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.
 
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
Click to expand...


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


--ron
 
Back
Top