Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I isolate the last two words in a text string?
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
isolate date from a text string into another cell | Excel Discussion (Misc queries) | |||
Extracting the last set of words from a text string | Excel Worksheet Functions | |||
How do I do a multiple search using key words in a text string | Excel Discussion (Misc queries) | |||
Alphabetically reorder a text string with multiple words | Excel Worksheet Functions | |||
Alphabetically reorder a text string with multiple words | Excel Worksheet Functions |