Select the last word of a string

  • Thread starter Thread starter et
  • Start date Start date
E

et

Hi all,

Could you anybody help to tell me how to extract the last word of a string
in a cell ?

for example :

Column A Column B
Bob Phillips Phillips
Lee John John

Thanks
Eling
 
Hi

personally i wouldn't use a formula for this .. if all of the cells contain
two words, i would use data / text to columns.

if you want column A to have both the first and last names and column b to
have last names only i would approach it this way:

Insert a blank column to the right of your list of names and copy the
contents of column A to it
(now you have two complete lists)
ensure that you have a blank column in column C
select column B
choose
data / text to columns
choose delimited - next
untick tab, tick space
finish

now delete column B and you should have what you're after.

However, if you would prefer a formula solution please post back.

Cheers
JulieD
 
Hi Eling,

Assuming just two words, then

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

If it could be any number of words, then try

=MID(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))+1,99)

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Bob Phillips wrote...
Assuming just two words, then

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

More robust,

=MID(TRIM(A1),FIND(" ",TRIM(A1))+1,256)
If it could be any number of words, then try

=MID(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1,
" ",""))))+1,99)
Click to expand...
....

An alternative approach involves using a defined name like seq
referring to =ROW(INDIRECT("1:1024")) in an array formula like
=MID(TRIM(A1),MAX(IF(MID(TRIM(A1),seq,1)=" ",seq))+1,256)
 
Back
Top