Find the First or Last So Many Words in a Text String

**snb** · 06-19-2012, 03:22 PM

Did you consider; to return the first 4 words:

PHP Code:


=LEFT(A1;FIND("@";SUBSTITUTE(A1;" ";"@";4)))

The N in your formula is 4 in mine (as an example)

PHP Code:


=LEFT(A1;FIND("@";SUBSTITUTE(A1;" ";"@";N)))

The last N words:

PHP Code:


=MID(TRIM(A1);FIND("@";SUBSTITUTE(TRIM(A1);" ";"@";1+LEN(A1)-LEN(SUBSTITUTE(TRIM(A1);" ";""))-3));LEN(A1))

**Rick Rothstein** · 06-20-2012, 02:31 AM

Originally Posted by snb

Did you consider; to return the first 4 words:

PHP Code:


=LEFT(A1;FIND("@";SUBSTITUTE(A1;" ";"@";4)))

The N in your formula is 4 in mine (as an example)

PHP Code:


=LEFT(A1;FIND("@";SUBSTITUTE(A1;" ";"@";N)))

Nope, at 5:00AM in the morning, that method simply didn't occur to me. Thanks for reminding me of it, although you should see my note in the next section.

Originally Posted by snb

The N in your formula is 4 in mine (as an example)

PHP Code:


=LEFT(A1;FIND("@";SUBSTITUTE(A1;" ";"@";N)))

This is a better formula than mine, for sure, but it is including a trailing space character. You need to subtract 1 from the FIND function's result...

PHP Code:


=LEFT(A1,FIND("@",SUBSTITUTE(A1," ","@",3))-1)

Originally Posted by snb

The last N words:

PHP Code:


=MID(TRIM(A1);FIND("@";SUBSTITUTE(TRIM(A1);" ";"@";1+LEN(A1)-LEN(SUBSTITUTE(TRIM(A1);" ";""))-3));LEN(A1))

In a similar fashion to your other formula, this one is including a leading space in its return value. However, with 6 more function calls than the formula I posted for this situation, I think I'll stay with my formula for this case.