Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
=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? |
#4
|
|||
|
|||
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? |
#5
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I want to return a value based on the status of a check box. If . | Excel Worksheet Functions | |||
Return only one instance | Excel Worksheet Functions | |||
Want to return a value based on a whether a number is within a ra. | Excel Worksheet Functions | |||
Formula to return cell contents based on multiple conditions | Excel Worksheet Functions | |||
Can VLOOKUP return multiple answers based on several identical lo. | Excel Worksheet Functions |