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.

Assuming at least three words, one way:

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

In article <D91E3361-FE55-4E60-B2ED-(E-Mail Removed)>,
Steve Gibbs <Steve (E-Mail Removed)> wrote:

> 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.

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Steve Gibbs" <Steve (E-Mail Removed)> wrote in message
news91E3361-FE55-4E60-B2ED-(E-Mail Removed)...
>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.

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.

"Steve Gibbs" wrote:

> 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.

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

In article <23E7D6E4-3920-401B-8887-(E-Mail Removed)>,
Steve Gibbs <(E-Mail Removed)> wrote:

> JE, I got an error message that there were too many arguments. I double and
> triple checked my typing.

On Sun, 23 Nov 2008 14:29:01 -0800, Steve Gibbs <Steve
(E-Mail Removed)> wrote:

>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

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" wrote:

> On Sun, 23 Nov 2008 14:29:01 -0800, Steve Gibbs <Steve
> (E-Mail Removed)> wrote:
>
> >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
>

On Fri, 28 Nov 2008 09:14:00 -0800, Steve Gibbs
<(E-Mail Removed)> wrote:

>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