CODEWITHSUNDEEP
excel
Jim Brockman
Jim Brockman

Reputation: 3

In excel, searching for the first instance of an array of words in a cell

I have a cell (bd5) with a long description of an event. I also have a list (Name Range= 'greeting') of things that should have happened early in the event. I want to know where in the cell (word count or character count) any of the array items first shows up.

=IFERROR(LOOKUP(2,1/SEARCH(greeting,B2),greeting),"") gives me the actual word not its location in cell b2

    A                  B                                  c              
1   Greeting      Transcript                           Greeting count    
2   My Name is    This is a long transcription of       Thank you for
                  a call. My agent should be starting   calling
                  with either "Thank you for Calling' 
                  or 'Good afternoon' before they pull
                  up the caller;s information and 
                  start troubleshooting
3   How May I 
    help you
4   Good morning
5   Good Afternoon
6   Thank you for 
    calling

I tried to recreate the example sheet. If I enter the formula I used above in C2 it results in "Thank you for calling" However I need to know where it falls in b2 . So a 16 would be best because it is the 16th word. However, 84 would as a character location.

Upvotes: 0

Views: 78

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60324

Given your data, I get 82 for the position of the matched string:

=AGGREGATE(15,6,SEARCH($A$2:$A$6,B2),1)

To get a word number (16 in your example) where the matched text is found:

=LEN(LEFT(B2,AGGREGATE(15,6,SEARCH($A$2:$A$6,B2),1)))+1-LEN(SUBSTITUTE(LEFT(B2,AGGREGATE(15,6,SEARCH($A$2:$A$6,B2),1))," ",""))

Upvotes: 2

Related Questions