Thread
:
How can I isolate the last two words in a text string?
View Single Post
#
6
Posted to microsoft.public.excel.misc
Ron Rosenfeld
external usenet poster
Posts: 5,651
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
Reply With Quote
Ron Rosenfeld
View Public Profile
Find all posts by Ron Rosenfeld