Finding one word

[CharlesF]

I have a long list of phrases consisting of two to four
words.

I want to extract the LAST word in each phrase.

I have tried =MID(N5,1,FIND(" ",N5,1)-1) which will give
the first word but can't figure out the next step.

All help gratefully received

Thanks

 

[Bernie Deitrick]

Charles,

The array formula, entered with Ctrl-Shift-Enter:

=RIGHT(A1,LEN(A1)-MAX((MID(A1,ROW(INDIRECT("1:"
& LEN(A1))),1)=" ")*ROW(INDIRECT("1:" & LEN(A1)))))

Remove the line feed to get it all on one line.

HTH,
Bernie
MS Excel MVP

 

[Frank Kabel]

Hi Charles
some possible solutions to get the last word of a cell:
1. Non array formula:
=MID(TRIM(A1),FIND("^",SUBSTITUTE(TRIM(A1),"
","^",LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))))+1,1024)

2. Array formulas
2.a) Array formula (proposed by Harlan Grove some days ago): To be
entered with CTRL+SHIFT+ENTER
=MID(TRIM(A1),MAX(IF(MID(TRIM(A1),Seq,1)=" ",Seq))+1,1024)
Where 'Seq' is a defined name(goto 'Insert - Name - Define') referring
to =ROW(INDIRECT("1:1024")).


2.b) Or even shorter / using less function calls (also by Harlan
Grove):
=MID(TRIM(A1),1024+2-MATCH(" ",MID(TRIM(A1),1024+1-Seq,1),0),1024)