How to return # characters based on 2nd instance of value
I have a range of text fields with descriptions containing a combination of
words and spaces. I want to return the leftmost two words of each description, however, the position of where the 2nd space occurs varies by cell. I have found a way to return the first word by using the following formula: =LEFT(E4,FIND(" ",E4)-1)) However, I cannot figure out how to return the TWO words. Can someone help? |
The third argument to Find() is optional, & it indicates which character
Excel should start searching from. You could use 1+ the position of the first space as the starting point =LEFT(E4,FIND(" ",E4,1+FIND(" ",E4,1))-1)) "JDay01" wrote: I have a range of text fields with descriptions containing a combination of words and spaces. I want to return the leftmost two words of each description, however, the position of where the 2nd space occurs varies by cell. I have found a way to return the first word by using the following formula: =LEFT(E4,FIND(" ",E4)-1)) However, I cannot figure out how to return the TWO words. Can someone help? |
=LEFT(A1,FIND("?",SUBSTITUTE(A1," ","?",2)))
"JDay01" wrote in message ... I have a range of text fields with descriptions containing a combination of words and spaces. I want to return the leftmost two words of each description, however, the position of where the 2nd space occurs varies by cell. I have found a way to return the first word by using the following formula: =LEFT(E4,FIND(" ",E4)-1)) However, I cannot figure out how to return the TWO words. Can someone help? |
One way:
=LEFT(E4,FIND("$",SUBSTITUTE(E4," ","$",2))-1) In article , JDay01 wrote: I have a range of text fields with descriptions containing a combination of words and spaces. I want to return the leftmost two words of each description, however, the position of where the 2nd space occurs varies by cell. I have found a way to return the first word by using the following formula: =LEFT(E4,FIND(" ",E4)-1)) However, I cannot figure out how to return the TWO words. Can someone help? |
JE & N - I like that. It's very concise.
"JE McGimpsey" wrote: One way: =LEFT(E4,FIND("$",SUBSTITUTE(E4," ","$",2))-1) In article , JDay01 wrote: I have a range of text fields with descriptions containing a combination of words and spaces. I want to return the leftmost two words of each description, however, the position of where the 2nd space occurs varies by cell. I have found a way to return the first word by using the following formula: =LEFT(E4,FIND(" ",E4)-1)) However, I cannot figure out how to return the TWO words. Can someone help? |
All times are GMT +1. The time now is 07:38 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com