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

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.

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

Assuming at least three words, one way:

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

In article ,
Steve Gibbs <Steve 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.

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

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Steve Gibbs" <Steve wrote in message
...
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.

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

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.

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

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

In article ,
Steve Gibbs wrote:

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

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

On Sun, 23 Nov 2008 14:29:01 -0800, Steve Gibbs <Steve
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

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

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

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

On Fri, 28 Nov 2008 09:14:00 -0800, Steve Gibbs
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