CODEWITHSUNDEEP
excelexcel-formula
user3503064
user3503064

Reputation: 21

Find first and second match of a list in Excel

I have text cells in column and i have a list of words, i need to get the first and second match of text cell within this list of word using formula without vba.

here is an example of the result

Upvotes: 0

Views: 533

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

If your text cells start in A2, then:

First Match  B2:  =IFERROR(MID($A2,AGGREGATE(15,6,SEARCH(LIST,$A2),COLUMNS($A:A)),FIND(" ",$A2&" ",AGGREGATE(15,6,SEARCH(LIST,$A2),COLUMNS($A:A)))-AGGREGATE(15,6,SEARCH(LIST,$A2),COLUMNS($A:A))),"")

and fill right one cell to get the 2nd Match. Then fill down as far as needed.

enter image description here

EDIT: The OP has added an additional requirement having to do with excluding words within words, eg do NOT find also if the word is Calso; and also do not return punctuation.

Although cumbersome in formulas, this can be handling by - Replacing all of the punctuation with space - Adding a space at the beginning and end of the sentence - Adding a space at the beginning and end of each word in LIST - adjusting the formula to not return the extra space.

The above can be done most simply by modifying the defined name LIST and also by using a defined name for a formula to do the punctuation replacement and space prefix and suffix.

Given the example above, we redefine LIST

LIST refers to: =" " & Sheet1!$F$2:$F$6 & " "

and, with some cell in Row 2 selected, we define theSentence

theSentence refers to:  =" " & TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Sheet1!$A10,","," "),"'"," "),"."," "),"!"," "))& " "

That particular definition will remove comma, apostrophe, period and exclamation point. If you need to remove other punctuation, you can just nest more SUBSTITUTE's

And we make some changes in the formula in B2:

B2: =IFERROR(MID(theSentence,1+AGGREGATE(15,6,SEARCH(LIST,theSentence),COLUMNS($A:A)),FIND(" ",theSentence,1+AGGREGATE(15,6,SEARCH(LIST,theSentence),COLUMNS($A:A)))-AGGREGATE(15,6,SEARCH(LIST,theSentence),COLUMNS($A:A))-1),"")

Upvotes: 1

Tom Sharpe
Tom Sharpe

Reputation: 34210

This is the second interpretation of the question (find the first and second match in the list of a string anywhere in the text).

=IFERROR(INDEX($F$2:$F$6,SMALL(IF(ISNUMBER(FIND(" "&$F$2:$F$6&" "," "&$A2&" ")),ROW($F$2:$F$6)-ROW($F$1)),COLUMNS($A1:A1))),"")

Note that this is an array formula and must be entered with CtrlShiftEnter

enter image description here

Upvotes: 1

Related Questions