How can I isolate the last two words in a text string?

[Steve Gibbs]

I have found a formula that works great for retrieving the last word from a
string of charactors in a cell.
If the string in Cell A1 is: "How do I return the last two words?", the
formula below will give me the last word. (Isn't it nice sometimes to have
the last word?)

{=RIGHT(A1,MATCH(" ",MID(A1,LEN(A1)-ROW(INDIRECT("1:"&LEN(A1))),1),0))}
This will return "words?".

How can I modify this or, what other formula will return, "two words?".

I think this is a very slick formula, but I don't quite understand it well
enough to figure out how to modify the match to find the second blank.

Note: I found this formula in Osborne, THE COMPLETE REFERENCE EXCEL 2000 by
Kathy Ivens and Conrad Carlberg.

 

[JE McGimpsey]

Assuming at least three words, one way:

=MID(A1, FIND("^", SUBSTITUTE(A1," ","^",LEN(A1) -
LEN(SUBSTITUTE(A1," ", "")) - 1)) + 1, 255)

 

[Niek Otten]

=RIGHT(A1,LEN(A1)-FIND("\",SUBSTITUTE(A1," ","\",LEN(A1)-LEN(SUBSTITUTE(A1,"
",))-1)))

 

[Steve Gibbs]

Thank you Niek, Your solution works great!
JE, I got an error message that there were too many arguments. I double and
triple checked my typing. Your solution is different than Niek's. I would
like to see yours work. Please check the solution. If it is right, then I
will try it again. Thanks.

 

[JE McGimpsey]

Quadruple check it (or paste from the message). It works.

 

[Ron Rosenfeld]

On Sun, 23 Nov 2008 14:29:01 -0800, Steve Gibbs <Steve

I have found a formula that works great for retrieving the last word from a
string of charactors in a cell.
If the string in Cell A1 is: "How do I return the last two words?", the
formula below will give me the last word. (Isn't it nice sometimes to have
the last word?)

{=RIGHT(A1,MATCH(" ",MID(A1,LEN(A1)-ROW(INDIRECT("1:"&LEN(A1))),1),0))}
This will return "words?".

How can I modify this or, what other formula will return, "two words?".

I think this is a very slick formula, but I don't quite understand it well
enough to figure out how to modify the match to find the second blank.

Note: I found this formula in Osborne, THE COMPLETE REFERENCE EXCEL 2000 by
Kathy Ivens and Conrad Carlberg.

So long as your words are some reasonable length, something like:

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),198))

will return the last two words.
--ron

 

[Steve Gibbs]

I recieved four different solutions to my problem. All four work. This
solution is the shortest, least complicated and works the best. Testing the
solution with different strings consisting of one word, two word, three word,
and longer sentances, this is the only solution that would return a single
word in an one word sentance. If a blind space exists at the end of a word
only Niek Otten's solution would return the last two words. If there are two
blind spaces ant the end of the sentance none of the solutions return any
words. For my purpose Ron Rosenfeld's solution was best. Thanks.

 

[Ron Rosenfeld]

I recieved four different solutions to my problem. All four work. This
solution is the shortest, least complicated and works the best. Testing the
solution with different strings consisting of one word, two word, three word,
and longer sentances, this is the only solution that would return a single
word in an one word sentance. If a blind space exists at the end of a word
only Niek Otten's solution would return the last two words. If there are two
blind spaces ant the end of the sentance none of the solutions return any
words. For my purpose Ron Rosenfeld's solution was best. Thanks.

Thanks for the feedback.

Please make this small change in my contribution -- it will get rid of the
trailing spaces problem and should have been there originally:

=TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),198))

--ron