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

[Rick Rothstein]

You are absolutely right about the 'trailing' space. I'd prefer

=trim(LEFT(A1,FIND("@",SUBSTITUTE(A1," ","@",3)))) 

Correct me if I'm wrong but shouldn't you use

=TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),N*99)) 

just to exclude a textstring that contains two adjacent spaces ?

Yes, that is a good catch, but unfortunately for you, it appears to mean that your suggested replacement for my first N words formula is no longer better than mine. Your formula suffers from the same problem with respect to multiple spaces that you pointed out mine does and it seems the only way to correct for them would be to include two TRIM function calls...

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

I considered your originally suggested formula better than mine because it involved one less function call, but now, with the two TRIM function calls added in to control the double-space problem, your suggested formula has the same number of function calls than my original proposal with your sugggested TRIM function added in. I like the symmetry of shape for the two functions I posted originally, so I think I will stay with them. Thanks for having caught this double-space problem in the first place though, I appreciate your diligence in sticking with this thread. I will modify the formulas in my original message to include the TRIM function as soon as I send this message.