Selecting last word in a string

[Peter Andrews]

I have a list of names in column A, e.g. A1 contains Mr. & Mrs. Homer
Simpson, A2 Mr. F. Smith, etc.

I want to extract the last name so the B1 contains Simpson, B2 Smith, etc.

I'm sure it can't be that difficult but after two hours of trying...


Thanks,

Peter

 

[Harlan Grove]

Peter Andrews wrote...

I have a list of names in column A, e.g. A1 contains Mr. & Mrs. Homer
Simpson, A2 Mr. F. Smith, etc.

I want to extract the last name so the B1 contains Simpson, B2 Smith, etc.

I'm sure it can't be that difficult but after two hours of trying...

If you're doing this once, it's likely you're doing it repeatedly. If
so, define the name seq referring to

=ROW(INDEX($1:$65536,1,1):INDEX($1:$65536,256,1))

Then use formulas like

=MID(TRIM(G5),LOOKUP(2,1/(MID(TRIM(G5),seq,1)=" "),seq+1),256)

The other alternative is formulas like

=MID(TRIM(G5),FIND("·",SUBSTITUTE(TRIM(G5)," ","·",
LEN(TRIM(G5))-LEN(SUBSTITUTE(G5," ",""))))+1,256)

These both work by finding the last space before the last nonspace
character in the string, then returning everything to the right of that
space. The TRIM calls avoid problems when there are trailing spaces.

That said, it's generally much more complicated than this because some
people's surnames include multiple words, e.g., Oscar de la Hoya.

 

[Guest]

As long as the name you want is after the LAST SPACE in the string, try
something like this:

For text in A1:
B1: =RIGHT(A1,LEN(A1)-LOOKUP(LEN(A1),FIND("
",A1,ROW(INDEX($A:$A,1,1):INDEX($A:$A,LEN(A1),1)))))

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Peter Andrews]

Peter Andrews wrote...

I have a list of names in column A, e.g. A1 contains Mr. & Mrs. Homer
Simpson, A2 Mr. F. Smith, etc.

I want to extract the last name so the B1 contains Simpson, B2 Smith, etc.

I'm sure it can't be that difficult but after two hours of trying...

If you're doing this once, it's likely you're doing it repeatedly. If
so, define the name seq referring to

=ROW(INDEX($1:$65536,1,1):INDEX($1:$65536,256,1))

Then use formulas like

=MID(TRIM(G5),LOOKUP(2,1/(MID(TRIM(G5),seq,1)=" "),seq+1),256)

The other alternative is formulas like

=MID(TRIM(G5),FIND("·",SUBSTITUTE(TRIM(G5)," ","·",
LEN(TRIM(G5))-LEN(SUBSTITUTE(G5," ",""))))+1,256)

These both work by finding the last space before the last nonspace
character in the string, then returning everything to the right of that
space. The TRIM calls avoid problems when there are trailing spaces.

That said, it's generally much more complicated than this because some
people's surnames include multiple words, e.g., Oscar de la Hoya.


Thank you

=MID(TRIM(G5),FIND("·",SUBSTITUTE(TRIM(G5)," ","·",
LEN(TRIM(G5))-LEN(SUBSTITUTE(G5," ",""))))+1,256)

does just what I want... now to try and understand exactly how it works

Peter