Selecting last word in a string

  • Thread starter Thread starter Peter Andrews
  • Start date Start date
P

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

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

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