Results 1 to 7 of 7

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

  1. #1
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13

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

    Using from the formula I posted in my "Get Field from Delimited Text String" article and the formula Haseeb Avarakkan posted in response to my "Get "Reversed" Field from Delimited Text String" article as a base, here are formulas for finding the first N words and the last N words in a text string.

    First N Words: =TRIM(LEFT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),N*99))

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

    where N is the number of words you want to retrieve. The only caution is that the text in A1 must be less than 100 characters (that is what the 99 establishes). If your text is longer, then you would need to change the two 99's in each formula to a number equal to, or larger than, the maximum number of characters the text in A1 can have.
    Last edited by Rick Rothstein; 05-07-2014 at 07:05 PM.

  2. #2
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    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)) 
    Last edited by snb; 06-19-2012 at 06:07 PM.

  3. #3
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by snb View Post
    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.

    Quote Originally Posted by snb View Post
    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
    Quote Originally Posted by snb View Post
    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.

  4. #4
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    You are absolutely right about the 'trailing' space. I'd prefer

    PHP Code:
    =trim(LEFT(A1,FIND("@",SUBSTITUTE(A1," ","@",3)))) 
    Correct me if I'm wrong but shouldn't you use

    PHP Code:
    =TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),N*99)) 
    just to exclude a textstring that contains two adjacent spaces ?

  5. #5
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by snb View Post
    You are absolutely right about the 'trailing' space. I'd prefer

    PHP Code:
    =trim(LEFT(A1,FIND("@",SUBSTITUTE(A1," ","@",3)))) 
    Correct me if I'm wrong but shouldn't you use

    PHP Code:
    =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...

    PHP Code:
     =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.
    Last edited by Rick Rothstein; 06-21-2012 at 07:53 PM.

  6. #6
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    Well, I never suggested anything 'better'; I prefer talking in 'alternatives'. But still I think my formula hasn't the 99 words limitation your suggestion has. How to assess that (lack) of limitation is a question of personal preference in my opinion.

  7. #7
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by snb View Post
    But still I think my formula hasn't the 99 words limitation your suggestion has.
    That is a strong point in favor of your formula, no doubt (and many will probably prefer it for that reason), but, as I mentioned, I still kind of like the symmetry of contruction between the two formulas I posted (just swap out RIGHT for LEFT or vice-versa depending on which end you want to count from).

Similar Threads

  1. Get Field from Delimited Text String
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 5
    Last Post: 01-05-2017, 01:24 AM
  2. Replies: 14
    Last Post: 05-25-2013, 06:55 AM
  3. Extract Certain Characters From A Text String
    By bobkap in forum Excel Help
    Replies: 5
    Last Post: 05-24-2013, 06:25 AM
  4. Find Color in the string using formula.
    By LalitPandey87 in forum Excel Help
    Replies: 4
    Last Post: 07-10-2012, 09:16 PM
  5. Replies: 6
    Last Post: 09-26-2011, 07:39 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •