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" <[email protected]> wrote in message
news:[email protected]...
>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 <[email protected]>,
JDay01 <[email protected]> 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 <[email protected]>,
> JDay01 <[email protected]> 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?
>